触发器

1.创建存储过程的基本语法模板：

1 if (exists (select * from sys.objects where name = 'pro_name'))
2     drop proc pro_name
3 go
4 create proc pro_name
5     @param_name param_type [=default_value]
6 as
7 begin    
8     sql语句
9 end

2.调用方法：

exec dbo.USP_GetAllUser 2;

3.查看本数据库中存在的存储过程

4.修改存储过程

alter proc proc_name
as
　　sql语句

5.存储过程中的输出参数的使用

 1 if (exists(select * from  sys.objects where name='GetUser'))
 2     drop proc GetUser
 3 go 
 4 create proc GetUser
 5     @id int output,
 6     @name varchar(20) out
 7 as 
 8 begin 
 9     select @id=Id,@name=Name from UserInfo where Id=@id
10 end
11 
12 go 
13 declare 
14 @name varchar(20),
15 @id int;
16 set @id=3;
17 exec dbo.GetUser @id,@name out;
18 select @id,@name;
19 print Cast(@id as varchar(10))+'-'+@name;

ps:参数output为该参数可以输出

6.分页获取数据的存储过程

 1 if (exists(select * from  sys.objects where name='GetUserByPage'))
 2     drop proc GetUserByPage
 3 go 
 4 create proc GetUserByPage
 5     @pageIndex int,
 6     @pageSize int
 7 as 
 8 declare 
 9 @startIndex int,
10 @endIndex int;
11 set @startIndex =  (@pageIndex-1)*@pageSize+1;
12 set @endIndex = @startIndex + @pageSize -1 ;
13 begin 
14     select Id,Name from 
15     (
16         select *,row_number()over (order by Id)as number from UserInfo  
17     )t where t.number>=@startIndex and t.number<=@endIndex
18 end
19 
20 go 
21 exec dbo.GetUserByPage 2,4;

7.存储过程中事务的创建

 1 if (exists(select * from sys.objects where name='JayJayToTest'))
 2 drop proc JayJayToTest
 3 go 
 4 create proc JayJayToTest
 5 @GiveMoney int,
 6 @UserName nvarchar(20)
 7 as 
 8 beginset nocount on;
 9 begin tran;
10 begin try
11 update BankTest set Money = Money-@GiveMoney where Name=@UserName;
12 update BankTest set Money = Money+@GiveMoney where Name='test';
13 commit;
14 end try 
15 begin catch 
16 rollback tran;
17 print ('发生异常，事务进行回滚');
18 end catch 
19 end
20 go
21 exec JayJayToTest 10,'jayjay'

8.了解存储过程的执行计划

SELECT * FROM sys.[syscacheobjects]查看当前缓存的执行计划