sql存储过程

--系统存储过程
xp_cmdshell 'dir c:'
xp_cmdshell 'net user abc password:123 /add'

xp_cmdshell 'md c:\abc'


sp_databases
exec sp_renamedb 'demo','test'



exec sp_tables

exec sp_helptext CK_Student_sAge
exec sp_helptext v_Student
exec sp_helptext 'sys.objects'


sp_attach_db
--分离数据库
use master
exec sp_detach_db myschool


exec sp_detach_db test
exec sp_attach_db @dbname='test',@filename1='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\test.mdf',@filename2='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\test_log.LDF'


--创建存储过程
if exists(select * from sys.objects where name='usp_upGrade')
	drop procedure usp_upGrade
go
create procedure usp_upGrade
as 
	declare @count int,@failCount int
	select @count=count(*) from score
	select @failCount=count(*) from score where english < 60
	while(@failCount > @count/2)
		begin
			update score set english = english + 2 
			select @failCount=count(*) from score where english < 60
		end
	update score set english = 100 where  english > 100
go

exec sp_helptext usp_upGrade


--调用存储过程
exec usp_upGrade

select * from score







--带参数的存储过程
--带默认值参数
if exists(select * from sys.objects where name='usp_upGrade')
	drop proc usp_upGrade
go
create proc usp_upGrade
	@passEnglish float = 60,
	@passMath float = 60
as
	declare @count int,@english int,@math int
	select @count=count(*) from score
	select @english=count(*) from score where english < @passEnglish
	select @math=count(*) from score where math < @passMath
	print '英语不及格的人数' + Convert(varchar,@english)
	print '数学不及格人数' + Convert(varchar,@math)

exec usp_upGrade 60,30

exec usp_upGrade @passEnglish=70,@passMath=30
--英语及格分数线用默认值
exec usp_upGrade @passMath=30





--存储过程输出值
if exists(select * from sys.objects where name='usp_upGrade')
	drop proc usp_upGrade
go
create proc usp_upGrade
	@passEnglish float = 60,
	@passMath float = 60
as
	declare @count int,@english int,@math int
	select @count=count(*) from score
	select @english=count(*) from score where english < @passEnglish
	select @math=count(*) from score where math < @passMath
	print '英语不及格的人数' + Convert(varchar,@english)
	print '数学不及格人数' + Convert(varchar,@math)
	return @count

--调用有返回值的存储过程
declare @num int
exec @num = usp_upGrade 
print @num




--带输出参数的存储过程
if exists(select * from sys.objects where name='usp_upGrade')
	drop proc usp_upGrade
go
create proc usp_upGrade
	@c int output,
	@e int output,
	@m int output,
	@passEnglish float = 60,
	@passMath float = 60
	
as
	declare @count int,@english int,@math int
	select @count=count(*) from score
	select @english=count(*) from score where english < @passEnglish
	select @math=count(*) from score where math < @passMath
	print '英语不及格的人数' + Convert(varchar,@english)
	print '数学不及格人数' + Convert(varchar,@math)
	set @c = @count
	set @e = @english
	set @m = @math

--调用有输出参数的存储过程
declare @count int,@english int,@math int

exec usp_upGrade @count output,@english output,@math output

select @count,@english,@math


select * from student

--分页存储过程

if exists(select * from sys.objects where name='usp_GetPageData')
	drop proc usp_GetPageData
go
create proc usp_GetPageData
	@pageSize int,	--一页多少条数据
	@pageIndex int, --第几页
	@pageCount int output	--共多少页
as
	declare @count int

	select * from 
	(select row_number() over(order by sId desc) as num,* from student) as t
	 where num between (@pageSize*(@pageIndex-1) + 1) and (@pageSize*@pageIndex)
	order by sId desc

	select @count = count(*) from student
	--求总共多少页
	set @pageCount = ceiling(@count/convert(float,@pageSize))


--
select * from student

declare @count int
exec usp_GetPageData 3,3,@count output
print @count

select ceiling(7/convert(float,3))



--实现登陆的存储过程
if exists(select * from sys.objects where name='usp_Login')
	drop proc usp_Login
go
create proc usp_Login
	@name varchar(10),
	@pwd varchar(10),
	@isLogin int output   --1 登陆成功 2用户名错误 3密码错误 4密码错误超过3次
as
	declare @times int  --错误次数
	--根据用户名是否存在
	if exists(select * from [user] where uUserName=@name)
		begin
			select @times = uTimes from [user] where uUserName=@name
			if(@times = 3)
				--密码错误3次
				set @isLogin=4
			else
				begin
					if exists(select * from [user] where uUserName=@name and uPwd=@pwd)
						begin
							--用户名密码正确 登陆成功
							set @isLogin=1
							update [user] set uTimes=0 where uUserName=@name
						end
					else
						begin
							--密码错误
							set @isLogin=3
							update [user] set uTimes=uTimes + 1 where uUserName=@name
						end
				end
		end
	else
		--用户名不存在
		set @isLogin= 2



declare @login int

--调用登陆存储过程
exec usp_Login 'admin','123',@login output
print @login
select * from [user]


update [user] set uTimes=0 where uid=6