SQL Server创建存储过程——动态SQL

James

　　简介：
　　存储过程(stored procedure)是一组为了完成特定功能的SQL语句集合，经编译后存储在服务器端的数据库中，利用存储过程可以加速SQL语句的执行。
　　自定义存储过程，由用户创建并能完成某一特定功能的存储过程，存储过程既可以有参数又有返回值，但是它与函数不同，存储过程的返回值只是指明执行是否成功，
　　存储过程并不能像函数那样被直接调用，只能利用 execute 来执行存储过程。
　　优点：
　　1、提高应用程序的通用性和可移植性：存储过程创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。并且数据库专业人员可以随时对存储过程进行
　　修改，且对程序源代码没有影响，这样就极大的提高了程序的可移植性。
　　2、可以提高SQL的速度，存储过程是编译过的，如果某一个操作包含大量的SQL代码或分别被执行多次，那么使用存储过程比直接使用单条SQL语句执行速度快的多。
　　3、减轻服务器的负担：当用户的操作是针对数据库对象的操作时，如果使用单条调用的方式，那么网络上还必须传输大量的SQL语句，如果使用存储过程，
　　则直接发送过程的调用命令即可，降低了网络的负担。
　　语法：



CREATE PROC [ EDURE ] procedure_name [ ; number ]
     [ { @parameter data_type }
         [ VARYING ] [ = default ] [ OUTPUT ]
     ] [ ,...n ]
[ WITH
     { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS
[ begin ]
     T-SQL 语句
[ end ]
　　无参数存储过程：




create procedure GetStuCou
as
begin
     select *
     from Student s
     left join Course c on s.C_S_Id=c.C_Id
end

execute GetStuCou
　　有返回值的存储过程：




create procedure GetStuCou_Re
as
begin
     insert into Course(C_Name) values('HTML5')
     return SCOPE_IDENTITY();        
end

execute GetStuCou_Re
　　有输入参数的存储过程：




create procedure GetStuCou_In
@StuNo    nvarchar(64)='001'        
as
begin
     select * from Student where S_StuNo=@StuNo
end

execute GetStuCou_In

execute GetStuCou_In '005'
　　有输入、输出参数的存储过程：




create procedure GetStuCou_Out
@StuNo    nvarchar(64),
@Height nvarchar(32) output
as
begin
     if(@StuNo is not null and @StuNo <> '')
     begin
         select @Height=S_Height
         from Student
         where S_StuNo=@StuNo
     end
     else
     begin
         set @Height='185'
     end
end

execute GetStuCou_Out '005',null
　　有输入、输出参数和结果集的存储过程：




create procedure GetStuCou_DS
@StuNo    nvarchar(64),
@Height nvarchar(32) output
as
begin
     if(@StuNo is not null and @StuNo <> '')
     begin
         select @Height=S_Height
         from Student
         where S_StuNo=@StuNo
     end
     else
     begin
         set @Height='185'
     end
     select s.S_Id,s.S_StuNo,s.S_Name,s.S_Sex,s.S_Height,s.S_BirthDate,c.C_Id,c.C_Name
     from Student s
     left join Course c on s.C_S_Id=c.C_Id
     where S_StuNo=@StuNo
end

execute GetStuCou_DS '005',null
　　返回多个结果集的存储过程：




create procedure GetStuCou_DSS
@StuNo    nvarchar(64),
@Height nvarchar(32)
as
begin
     if(@StuNo is not null and @StuNo <> '')
     begin
         select *
         from Student
         where S_StuNo=@StuNo
     end
     if(@Height is not null and @Height <> '')
     begin
         select *
         from Student
         where S_Height=@Height
     end
end

execute GetStuCou_DSS '005','185'
　　存储过程里面不仅可以进行查询，还可以进行各种增删改操作。其实存储就是由很多 T-SQL 语句组成的代码块。
　　存储过程中创建变量、赋值变量、创建表变量和临时表：




create procedure GetStuCou_Ext
@StuNo    nvarchar(64),
@Height nvarchar(32)
as
begin
     declare @Var nvarchar(10)   
     set @Var='123'        
     
     declare @StuTab table
     (
         ID     int not null primary key,
         StuNo    nvarchar(50) unique,
         Name varchar(50),
         Sex varchar(10),
         Height varchar(10)
     )
     
     
     create table #Tab
     (
         ID     int not null primary key,
         StuNo    nvarchar(50),
         Name varchar(50),
         Sex varchar(10),
         Height varchar(10)
     )

     alter table #Tab add constraint S_UNIQUE unique(StuNo)

     
     if(@StuNo is not null and @StuNo <> '')
     begin
         insert into @StuTab(ID,StuNo,Name,Sex,Height)   
         select S_Id,S_StuNo,S_Name,S_Sex,S_Height
         from Student
         where S_StuNo=@StuNo
         insert into #Tab(ID,StuNo,Name,Sex,Height)   
         select S_Id,S_StuNo,S_Name,S_Sex,S_Height
         from Student
         where S_StuNo=@StuNo
     end
     if(@Height is not null and @Height <> '')
     begin
         insert into @StuTab(ID,StuNo,Name,Sex,Height)   
         select S_Id,S_StuNo,S_Name,S_Sex,S_Height
         from Student
         where S_Height=@Height
         insert into #Tab(ID,StuNo,Name,Sex,Height)   
         select S_Id,S_StuNo,S_Name,S_Sex,S_Height
         from Student
         where S_Height=@Height
     end
     SELECT * FROM @StuTab
     select * from #Tab
end

execute GetStuCou_Ext '005','185'
　　存储过程动态执行 SQL 语句：
　　以上可以看出我们传入的参数（学号）是单个的，那么如果一次性传入多个学号呢（使用逗号隔开，即 '005,006,007' ），这就需要用到动态拼接并执行 sql 语句。



create proc GetStus
@StuNo nvarchar(500)
as
begin
     declare @Sql nvarchar(3000)

     if(@StuNo is not null and @StuNo <> '')
     begin
         set @Sql=' select * from Student where S_StuNo in ('+@StuNo+') '
     end
     exec (@Sql)   
end
exec GetStus '003,005,009'        
　　现在还有一个问题，我想要执行动态 sql 的时候并返回变量值，比如我现在需要执行动态 sql 的时候返回课程 ID ,然后根据课程 ID 查询课程。
　　使用系统存储过程 sp_executesql 动态 sql 给变量赋值。
　　修改存储过程之后：



ALTER proc [dbo].[GetStus]
@StuNo nvarchar(500)
as
begin
     declare @Sql nvarchar(3000)
     declare @C_Id int
     declare @Cou int
     if(@StuNo is not null and @StuNo <> '')
     begin
         set @Sql=' select @CId=C_S_Id,@cou=count(1) from Student where S_StuNo in ('+@StuNo+') group by C_S_Id '
     end
     exec sp_executesql @Sql,N'@CId int output,@cou int output',@CId = @C_Id output,@cou = @Cou output;

     select @C_Id,@Cou   
     select * from Course where C_Id=@C_Id
end
exec GetStus '005'        

　　PS：sp_executesql 要求动态 Sql 和动态 Sql 参数列表必须是 NVARCHAR 类型。
　　动态Sql的参数列表与外部提供值的参数列表顺序必需一致，例如：N'@CId int output,@cou int output',@CId = @C_Id output,@cou = @Cou output;，@CId 对应 @C_Id，@cou 对应 @Cou。
　　动态SQl的参数列表与外部提供参数的参数列表参数名可以同名，如果不一样，需显示注明，例如：N'@CId int output,@cou int output',@CId = @C_Id output,@cou = @Cou output;，即 @CId = @C_Id 和 @cou = @Cou 。