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

系统管理员 2023-08-17 17:49 128阅读 0赞

**简介：**

存储过程(stored procedure)是一组为了完成特定功能的SQL语句集合，经编译后存储在服务器端的数据库中，利用存储过程可以加速SQL语句的执行。

自定义存储过程，由用户创建并能完成某一特定功能的存储过程，存储过程既可以有参数又有返回值，但是它与函数不同，存储过程的返回值只是指明执行是否成功，

存储过程并不能像函数那样被直接调用，只能利用 execute 来执行存储过程。

**优点：**

1、提高应用程序的通用性和可移植性：存储过程创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。并且数据库专业人员可以随时对存储过程进行

修改，且对程序源代码没有影响，这样就极大的提高了程序的可移植性。

2、可以提高SQL的速度，存储过程是编译过的，如果某一个操作包含大量的SQL代码或分别被执行多次，那么使用存储过程比直接使用单条SQL语句执行速度快的多。

3、减轻服务器的负担：当用户的操作是针对数据库对象的操作时，如果使用单条调用的方式，那么网络上还必须传输大量的SQL语句，如果使用存储过程，

则直接发送过程的调用命令即可，降低了网络的负担。

**语法：**

![复制代码][copycode.gif]

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 ]

![复制代码][copycode.gif]

**无参数存储过程：**

![复制代码][copycode.gif]

1 --创建名为 GetStuCou 的无参数存储过程
     2 create procedure GetStuCou
     3 as
     4 begin
     5     select * 
     6     from Student s
     7     left join Course c on s.C_S_Id=c.C_Id
     8 end
     9 
    10 --执行名为 GetStuCou 的无参数存储过程
    11 execute GetStuCou

![复制代码][copycode.gif]

**有返回值的存储过程：**

![复制代码][copycode.gif]

1 --创建名为 GetStuCou_Re 的有返回值的存储过程
     2 create procedure GetStuCou_Re
     3 as
     4 begin
     5     insert into Course(C_Name) values('HTML5')
     6     return SCOPE_IDENTITY();        -- 返回为当前表插入数据最后生成的标识值。
     7 end
     8 
     9 --执行名为 GetStuCou 的有返回值的存储过程
    10 execute GetStuCou_Re

![复制代码][copycode.gif]

**有输入参数的存储过程：**

![复制代码][copycode.gif]

1 --创建名为 GetStuCou_In 的有输入参数的存储过程
     2 create procedure GetStuCou_In
     3 @StuNo    nvarchar(64)='001'        --设置默认值
     4 as
     5 begin
     6     select * 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'

![复制代码][copycode.gif]

**有输入、输出参数的存储过程：**

![复制代码][copycode.gif]

1 --创建名为 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         select @Height=S_Height 
    10         from Student 
    11         where S_StuNo=@StuNo
    12     end
    13     else
    14     begin
    15         set @Height='185'
    16     end
    17 end
    18 
    19 --执行名为 GetStuCou_Out 的有输入参数和输出参数的存储过程
    20 execute GetStuCou_Out '005',null

![复制代码][copycode.gif]

**有输入、输出参数和结果集的存储过程：**

![复制代码][copycode.gif]

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         select @Height=S_Height 
    10         from Student 
    11         where S_StuNo=@StuNo
    12     end
    13     else
    14     begin
    15         set @Height='185'
    16     end
    17 
    18     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
    19     from Student s
    20     left join Course c on s.C_S_Id=c.C_Id
    21     where S_StuNo=@StuNo
    22 end
    23 
    24 --执行名为 GetStuCou_DS 的有输入参数、输出参数和结果集的存储过程
    25 execute GetStuCou_DS '005',null

![复制代码][copycode.gif]

**返回多个结果集的存储过程：**

![复制代码][copycode.gif]

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         select *
    10         from Student 
    11         where S_StuNo=@StuNo
    12     end
    13 
    14     if(@Height is not null and @Height <> '')
    15     begin
    16         select * 
    17         from Student
    18         where S_Height=@Height
    19     end
    20 end
    21 
    22 --执行名为 GetStuCou_DSS 的返回多个结果集的存储过程
    23 execute GetStuCou_DSS '005','185'

![复制代码][copycode.gif]

存储过程里面不仅可以进行查询，还可以进行各种增删改操作。其实存储就是由很多 T-SQL 语句组成的代码块。

存储过程中创建变量、赋值变量、创建表变量和临时表：

![复制代码][copycode.gif]

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     set @Var='123'        --赋值变量
    10 
    11     --定义表变量
    12     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     (
    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         insert into @StuTab(ID,StuNo,Name,Sex,Height)    --把数据插入表变量
    39         select S_Id,S_StuNo,S_Name,S_Sex,S_Height
    40         from Student 
    41         where S_StuNo=@StuNo
    42 
    43         insert into #Tab(ID,StuNo,Name,Sex,Height)    --把数据插入临时表
    44         select 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         insert into @StuTab(ID,StuNo,Name,Sex,Height)    --把数据插入表变量
    52         select S_Id,S_StuNo,S_Name,S_Sex,S_Height
    53         from Student
    54         where S_Height=@Height
    55 
    56         insert into #Tab(ID,StuNo,Name,Sex,Height)    --把数据插入临时表
    57         select 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     select * from #Tab
    64 end
    65 
    66 --执行名为 GetStuCou_DSS 的返回多个结果集的存储过程
    67 execute GetStuCou_Ext '005','185'

![复制代码][copycode.gif]

存储过程动态执行 SQL 语句：

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

![复制代码][copycode.gif]

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         set @Sql=' select * from Student where S_StuNo in ('+@StuNo+') '
    10     end
    11 
    12     exec (@Sql)    --执行动态 sql 
    13 end
    14 
    15 exec GetStus '003,005,009'        --执行存储过程 GetStus

![复制代码][copycode.gif]

现在还有一个问题，我想要执行动态 sql 的时候并返回变量值，比如我现在需要执行动态 sql 的时候返回课程 ID ,然后根据课程 ID 查询课程。

使用系统存储过程 sp\_executesql 动态 sql 给变量赋值。

修改存储过程之后：

![复制代码][copycode.gif]

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         set @Sql=' select @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     select @C_Id,@Cou    --查看返回变量的值
    17 
    18     select * from Course where C_Id=@C_Id 
    19 end
    20 
    21 exec GetStus '005'        --执行存储过程 GetStus

![复制代码][copycode.gif]

![1041184-20170418014922181-1582461866.png][]

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 。

转载于:https://www.cnblogs.com/jiangyunfeng/p/11385691.html

[copycode.gif]: /images/20230808/2f6d94a687214433b8f2696be5424f44.png
[1041184-20170418014922181-1582461866.png]: /images/20230808/598295286e234954abadd808dad87eb1.png