触发器

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

if (exists (select * from sys.objects where name = 'pro_name'))
    drop proc pro_name
go
create proc pro_name
    @param_name param_type [=default_value]
as
begin    
    sql语句
end

2.调用方法：

exec dbo.USP_GetAllUser 2;

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

 4.修改存储过程

alter proc proc_name
as
　　sql语句

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

if (exists(select * from  sys.objects where name='GetUser'))
    drop proc GetUser
go 
create proc GetUser
    @id int output,
    @name varchar(20) out
as 
begin 
    select @id=Id,@name=Name from UserInfo where Id=@id
end

go 
declare 
@name varchar(20),
@id int;
set @id=3;
exec dbo.GetUser @id,@name out;
select @id,@name;
print Cast(@id as varchar(10))+'-'+@name;

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

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

if (exists(select * from  sys.objects where name='GetUserByPage'))
    drop proc GetUserByPage
go 
create proc GetUserByPage
    @pageIndex int,
    @pageSize int
as 
declare 
@startIndex int,
@endIndex int;
set @startIndex =  (@pageIndex-1)*@pageSize+1;
set @endIndex = @startIndex + @pageSize -1 ;
begin 
    select Id,Name from 
    (
        select *,row_number()over (order by Id)as number from UserInfo  
    )t where t.number>=@startIndex and t.number<=@endIndex
end

go 
exec dbo.GetUserByPage 2,4;

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

if (exists(select * from sys.objects where name='JayJayToTest'))
drop proc JayJayToTest
go 
create proc JayJayToTest
@GiveMoney int,
@UserName nvarchar(20)
as 
beginset nocount on;
begin tran;
begin try
update BankTest set Money = Money-@GiveMoney where Name=@UserName;
update BankTest set Money = Money+@GiveMoney where Name='test';
commit;
end try 
begin catch 
rollback tran;
print ('发生异常，事务进行回滚');
end catch 
end
go
exec JayJayToTest 10,'jayjay'

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

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