SQLServer创建存储过程——动态SQL
简介：
存储过程(stored procedure)是⼀组为了完成特定功能的SQL语句集合，经编译后存储在服务器端的数据库中，利⽤存储过程可以加速SQL语句的执⾏。
⾃定义存储过程，由⽤户创建并能完成某⼀特定功能的存储过程，存储过程既可以有参数⼜有返回值，但是它与函数不同，存储过程的返回值只是指明执⾏是否成功，
ns存储过程并不能像函数那样被直接调⽤，只能利⽤ execute 来执⾏存储过程。
优点：
1、提⾼应⽤程序的通⽤性和可移植性：存储过程创建后，可以在程序中被多次调⽤，⽽不必重新编写该存储过程的SQL语句。并且数据库专业⼈员可以随时对存储过程进⾏
修改，且对程序源代码没有影响，这样就极⼤的提⾼了程序的可移植性。
2、可以提⾼SQL的速度，存储过程是编译过的，如果某⼀个操作包含⼤量的SQL代码或分别被执⾏多次，那么使⽤存储过程⽐直接使⽤单条SQL语句执⾏速度快的多。
3、减轻服务器的负担：当⽤户的操作是针对数据库对象的操作时，如果使⽤单条调⽤的⽅式，那么⽹络上还必须传输⼤量的SQL语句，如果使⽤存储过程，
则直接发送过程的调⽤命令即可，降低了⽹络的负担。
语法：
1 CREATE PROC [ EDURE ] procedure_name [ ; number ]
2 [ { @parameter data_type }
3 [ VARYING ] [ = default ] [ OUTPUT ]
4 ] [ ,...n ]
5 [ WITH
6 { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
7 [ FOR REPLICATION ]
8 AS
9 [ begin ]
10 T-SQL 语句
11 [ end ]
⽆参数存储过程：
1 --创建名为 GetStuCou 的⽆参数存储过程
2 create procedure GetStuCou
3 as
4 begin
5 lect *
6 from Student s
7 left join Cour c on s.C_S_Id=c.C_Id
8 end
9
10 --执⾏名为 GetStuCou 的⽆参数存储过程
11 execute GetStuCou
有返回值的存储过程：
1 --创建名为 GetStuCou_Re 的有返回值的存储过程
2 create procedure GetStuCou_Re
3 as
4 begin
5 inrt into Cour(C_Name) values('HTML5')
6 return SCOPE_IDENTITY(); -- 返回为当前表插⼊数据最后⽣成的标识值。
7 end
8
9 --执⾏名为 GetStuCou 的有返回值的存储过程
10 execute GetStuCou_Re
有输⼊参数的存储过程：
1 --创建名为 GetStuCou_In 的有输⼊参数的存储过程
adjectives
2 create procedure GetStuCou_In
3 @StuNo nvarchar(64)='001' --设置默认值
4 as
5 begin
6 lect * from Student where S_StuNo=@StuNo
7 end
8
9 --执⾏名为 GetStuCou_In 的有输⼊参数的存储过程（不传参数，即使⽤默认值）
10 execute GetStuCou_In
11
12 --执⾏名为 GetStuCou_In 的有输⼊参数的存储过程（传⼊参数）
13 execute GetStuCou_In '005'
有输⼊、输出参数的存储过程：
climate change1 --创建名为 GetStuCou_Out 的有输⼊参数和输出参数的存储过程
2 create procedure GetStuCou_Out
3 @StuNo nvarchar(64),
4 @Height nvarchar(32) output
5 as
6 begin
7 if(@StuNo is not null and @StuNo <> '')
8 begin
9 lect @Height=S_Height
10 from Student
11 where S_StuNo=@StuNo
12 end
13 el
14 begin
15 t @Height='185'
16 end
17 end
18
19 --执⾏名为 GetStuCou_Out 的有输⼊参数和输出参数的存储过程
20 execute GetStuCou_Out '005',null
有输⼊、输出参数和结果集的存储过程：
1 --创建名为 GetStuCou_DS 的有输⼊参数、输出参数和结果集的存储过程
2 create procedure GetStuCou_DS
3 @StuNo nvarchar(64),
4 @Height nvarchar(32) output
5 as
6 begin
7 if(@StuNo is not null and @StuNo <> '')
8 begin
9 lect @Height=S_Height
10 from Student
11 where S_StuNo=@StuNo
12 end
13 el
14 begin
15 t @Height='185'
16 end
17
18 lect s.S_Id,s.S_StuNo,s.S_Name,s.S_Sex,s.S_Height,s.S_BirthDate,c.C_Id,c.C_Name
19 from Student s
20 left join Cour c on s.C_S_Id=c.C_Idmolten
21 where S_StuNo=@StuNo
22 end
23
24 --执⾏名为 GetStuCou_DS 的有输⼊参数、输出参数和结果集的存储过程
25 execute GetStuCou_DS '005',null
返回多个结果集的存储过程：
暂停英文
1 --创建名为 GetStuCou_DSS 的返回多个结果集的存储过程
2 create procedure GetStuCou_DSS
3 @StuNo nvarchar(64),
4 @Height nvarchar(32)
5 as
6 begin
7 if(@StuNo is not null and @StuNo <> '')
8 begin
9 lect *
10 from Student
11 where S_StuNo=@StuNo
12 end
13
14 if(@Height is not null and @Height <> '')
15 begin
16 lect *
17 from Student
18 where S_Height=@Height
19 end
20 end
21
22 --执⾏名为 GetStuCou_DSS 的返回多个结果集的存储过程
23 execute GetStuCou_DSS '005','185'
存储过程⾥⾯不仅可以进⾏查询，还可以进⾏各种增删改操作。其实存储就是由很多 T-SQL 语句组成的代码块。
存储过程中创建变量、赋值变量、创建表变量和临时表：
1 --创建名为 GetStuCou_Ext 的返回多个结果集的存储过程
2 create procedure GetStuCou_Ext
3 @StuNo nvarchar(64),
4 @Height nvarchar(32)
武汉枫叶国际学校5 as
6 begin
7 declare @Var nvarchar(10) --定义变量
8
9 t @Var='123' --赋值变量
10
11 --定义表变量
karos12 declare @StuTab table
13 (
14 ID int not null primary key,
15 StuNo nvarchar(50) unique,
16 Name varchar(50),
17 Sex varchar(10),
18 Height varchar(10)
19 )
20 --表变量只能在定义的时候添加约束
21
22 --定义临时表
23 create table #Tab
24 (
adele skyfall
25 ID int not null primary key,
26 StuNo nvarchar(50),
27 Name varchar(50),
28 Sex varchar(10),
29 Height varchar(10)
30 )
31
32 alter table #Tab add constraint S_UNIQUE unique(StuNo)
33
34 --临时表可以在之后添加约束
35
36 if(@StuNo is not null and @StuNo <> '')
37 begin
38 inrt into @StuTab(ID,StuNo,Name,Sex,Height) --把数据插⼊表变量
39 lect S_Id,S_StuNo,S_Name,S_Sex,S_Height
美国富豪花式避税记录曝光40 from Student
41 where S_StuNo=@StuNo
42
43 inrt into #Tab(ID,StuNo,Name,Sex,Height) --把数据插⼊临时表
44 lect S_Id,S_StuNo,S_Name,S_Sex,S_Height
45 from Student
46 where S_StuNo=@StuNo
47 end
48
49 if(@Height is not null and @Height <> '')
50 begin
51 inrt into @StuTab(ID,StuNo,Name,Sex,Height) --把数据插⼊表变量
52 lect S_Id,S_StuNo,S_Name,S_Sex,S_Height
53 from Student
54 where S_Height=@Height
55
56 inrt into #Tab(ID,StuNo,Name,Sex,Height) --把数据插⼊临时表
57 lect S_Id,S_StuNo,S_Name,S_Sex,S_Height
58 from Student
59 where S_Height=@Height
60 end
61
62 SELECT * FROM @StuTab
63 lect * from #Tab
64 end
65
66 --执⾏名为 GetStuCou_DSS 的返回多个结果集的存储过程
67 execute GetStuCou_Ext '005','185'
存储过程动态执⾏ SQL 语句：
以上可以看出我们传⼊的参数（学号）是单个的，那么如果⼀次性传⼊多个学号呢（使⽤逗号隔开，即 '005,006,007' ），这就需要⽤到动态拼接并执⾏ sql 语句。
1 create proc GetStus
2 @StuNo nvarchar(500)
3 as
4 begin
5 declare @Sql nvarchar(3000)
6
7 if(@StuNo is not null and @StuNo <> '')
8 begin
9 t @Sql=' lect * from Student where S_StuNo in ('+@StuNo+') '
10 end
11
12 exec (@Sql) --执⾏动态 sql
13 end
14
15 exec GetStus '003,005,009' --执⾏存储过程 GetStus
现在还有⼀个问题，我想要执⾏动态 sql 的时候并返回变量值，⽐如我现在需要执⾏动态 sql 的时候返回课程 ID ，然后根据课程 ID 查询课程。
使⽤系统存储过程 sp_executesql 动态 sql 给变量赋值。
修改存储过程之后：
1 ALTER proc [dbo].[GetStus]
2 @StuNo nvarchar(500)
3 as
4 begin
5 declare @Sql nvarchar(3000)
6 declare @C_Id int
7 declare @Cou int
8
9 if(@StuNo is not null and @StuNo <> '')
10 begin
11 t @Sql=' lect @CId=C_S_Id,@cou=count(1) from Student where S_StuNo in ('+@StuNo+') group by C_S_Id '
12 end
13
14 exec sp_executesql @Sql,N'@CId int output,@cou int output',@CId = @C_Id output,@cou = @Cou output;
15
16 lect @C_Id,@Cou --查看返回变量的值
17
18 lect * from Cour where C_Id=@C_Id
当幸福来敲门下载19 end
20
21 exec GetStus '005' --执⾏存储过程 GetStus
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 。
