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

摘要:
ENCRYPTION}]7[FORREPLICATION]8AS9[开始]10T-SQL语句11[结束]无参数存储过程:

简介:

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

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

存储过程并不能像函数那样被直接调用,只能利用 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     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
复制代码

有返回值的存储过程:

复制代码
 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
复制代码

有输入参数的存储过程:

复制代码
 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'
复制代码

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

复制代码
 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 @ 
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         select @Height=S_Height 
10         from Student 
11         where S_StuNo=@StuNo
12     end
13     else
14     begin
15         set @ 
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
复制代码

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

复制代码
 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'
复制代码

存储过程里面不仅可以进行查询,还可以进行各种增删改操作。其实存储就是由很多 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     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'
复制代码

存储过程动态执行 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         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
复制代码

现在还有一个问题,我想要执行动态 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         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
复制代码

SQL Server创建存储过程——动态SQL第21张

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 。

免责声明:文章转载自《SQL Server创建存储过程——动态SQL》仅用于学习参考。如对内容有疑问,请及时联系本站处理。

上篇一个关于vue+mysql+express的全栈项目(三)------ 登录注册功能的实现(已经密码安全的设计)面向学生的美国国家教育技术标准(第二版)下篇

宿迁高防,2C2G15M,22元/月;香港BGP,2C5G5M,25元/月 雨云优惠码:MjYwNzM=

相关文章

C89标准和C99标准C11标准的区别

转载 C89标准和C99标准C11标准的区别 C99对C89的改变 1、增加restrict指针  C99中增加了公适用于指针的restrict类型修饰符,它是初始访问指针所指对象的惟一途径,因此只有借助restrict指针表达式才能访问对象。restrict指针指针主要用做函数变元,或者指向由malloc()函数所分配的内存变量。restrict数据类型...

win7 64位 安装 VS2010 SP1 失败的解决方法

1. 系统环境:Windows Server 2008 R2(与Win7 x64系统相似),VS2010及常用办公、编程工具等软件2. 问题症状:无论是通过在线安装包还是ISO安装包安装,多次安装,都在安装过程中回滚,最终安装失败。3. 产生原因:系统安装整一年,常年使用,系统自动补丁等操作,使得原系统盘C盘40G空间告急,于是动手删除了部分C:Windo...

c#Winform自定义控件-目录

前提 入行已经7,8年了,一直想做一套漂亮点的自定义控件,于是就有了本系列文章。 GitHub:https://github.com/kwwwvagaa/NetWinformControl 码云:https://gitee.com/kwwwvagaa/net_winform_custom_control.git 如果觉得写的还行,请点个 star 支持一...

Mysql基础

字段类型 mysql字段定义中INT(x)中的x仅仅指的是显示宽度。该可选显示宽度规定用于显示宽度小于指定的列宽度的值时从左侧填满宽度。显示宽度并不限制可以在列内保存的值的范围,也不限制超过列的指定宽度的值的显示。所以x的定义与存储空间没有任何关系都是4个字节。 超过最大数值不会报错但是根据此字段查询不到,也关联不起来。 查看mysql数据库编码 sho...

几款主流 NoSql 数据库的对比

最近小组准备启动一个 node 开源项目,从前端亲和力、大数据下的IO性能、可扩展性几点入手挑选了 NoSql 数据库,但具体使用哪一款产品还需要做一次选型。 我们最终把选项范围缩窄在 HBase、Redis、MongoDB、Couchbase、LevelDB 五款较主流的数据库产品中,本文将主要对它们进行分析对比。 鉴于缺乏项目中的实战经验沉淀,本文内容...

SAP 实例- 页签tabsrip

屏幕页签:项目上有一需求,对标准TCODE 一个屏幕增加一个页签。于是做了个例子。 下面屏幕有两个页签。    我们来看一下屏幕结构。100屏幕是主屏幕,101,102是子屏幕,对应页签test1,test2. 看一下屏幕属性 100屏幕:  101屏幕和102屏幕属性相同:  看一下屏幕制作器:区域1 是两个push button; 区域2是tab...