SQL Server 中 EXEC 与 SP_EXECUTESQL 的分

SQL Server 中 EXEC 与 SP_EXECUTESQL 的区别

 

MSSQL为我们提供了少于栽动态执行SQL语句之命令,分别是 EXEC
和 SP_EXECUTESQL ,我们先来拘禁一下少栽办法的用法。

预先成立一个发明,并加上一些多少来拓展现身说法:

SQL Server 1SQL Server 2

CREATE TABLE t_student(
 Id INT NOT NULL,
 Name NVARCHAR (10) NULL,
 Age TINYINT NULL,
 School NVARCHAR(20) NULL,
 Class NVARCHAR(10) NULL,
 Score FLOAT NULL,
 CONSTRAINT [PK_Student_Id] PRIMARY KEY CLUSTERED(Id)
)
GO

INSERT INTO t_student VALUES(1,'张小红',8,'育才小学','一班',92)
INSERT INTO t_student VALUES(2,'王丽丽',8,'育才小学','一班',90)
INSERT INTO t_student VALUES(3,'张燕',7,'云华小学','二班',86)
INSERT INTO t_student VALUES(4,'刘华',6,'云华小学','二班',85)

View Code

 

一、EXEC 

EXEC命令好履一个囤过程也可尽一个动态SQL语句。先来看望怎么实行存储过程:

新建一个囤过程 SP_GetStudent ,返回 成绩超乎90 分的学习者:

SQL Server 3SQL Server 4

CREATE PROCEDURE [dbo].[Sp_GetStudent]
    @Score FLOAT,
    @Nums INT OUTPUT 
AS
BEGIN
    SET NOCOUNT ON;
    SELECT * FROM t_student WHERE Score >=@Score
    SELECT @Nums=COUNT(1) FROM t_student WHERE Score >=@Score
    IF(@Nums>0)
     RETURN 1
    ELSE
     RETURN 0
END
GO

View Code

 

 该存储过程涉及了 查询操作、返回值和出口参数,我们来拘禁用EXEC
命令如何调用:

SQL Server 5SQL Server 6

DECLARE @return_value int,
        @OutNums int

EXEC    @return_value = [dbo].[Sp_GetStudent] 
        @Score = 90,
        @Nums = @OutNums OUTPUT

SELECT  @OutNums as N'大于90分的人数'

SELECT  '返回值' = @return_value
GO

View Code

 

SQL Server 7

 

咱发现EXEC
执行存储过程以及咱们平素程序实施一个法是几乎等同的,返回值参数
直接就是足以当存储过程的履后的返值,输出参数 在后头要增加 OUTPUT
关键字。

推行存储过程不是着重,重点是施行动态sql语句,同样看一下例证

SQL Server 8SQL Server 9

DECLARE @TableName NVARCHAR(50),@Sql NVARCHAR(MAX),@Score INT;
SET @TableName = 't_Student';
SET @Score = 90;
SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) +'WHERE Score >= '+CAST(@Score AS NVARCHAR(10))
EXEC (@sql);

View Code

SQL Server 10

 

只顾:在履行拼接SQL
语句之时段,的EXEC括号受特允许包含一个字符串变量,但是得串联多独变量,如果我们一直执行之SQL语句:

 

 

SQL Server 11SQL Server 12

--这是错误的调用
EXEC ('SELECT * FROM '+QUOTENAME(@TableName) +'WHERE Score >= '+CAST(@Score AS NVARCHAR(10)));

View Code

实施就会提醒错误。但是这样就是不曾问题:

 

SQL Server 13SQL Server 14

DECLARE @TableName NVARCHAR(50),@Sql NVARCHAR(MAX),@Score INT
DECLARE @Sql2 NVARCHAR(MAX)
SET @TableName = 't_Student';
SET @Score = 90;
SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName)
SET @Sql2=' WHERE Score >= '+CAST(@Score AS NVARCHAR(10))
EXEC (@sql+@sql2)

View Code

 

 EXEC 执行拼接sql语句的时候不支持 嵌入式参数,如下:

 

SQL Server 15SQL Server 16

DECLARE @OUT_Nums INT,@IN_Score INT,@Sql NVARCHAR(MAX)
SET @IN_Score = 90
SET @sql = 'SELECT @Nums=COUNT(1) FROM t_student WHERE Score >= @Score'
EXEC (@sql)

View Code

 

SQL Server 17

 

由此地方的代码发现,EXEC
执行拼接的SQL语句的时,不支持内嵌参数,包括输入参数和出口参数。有的上咱们怀念把获得的count(*)传出来,用EXEC是坏办到的。接下来,再来探视SP_EXECUTESQL的使用:

二、SP_EXECUTESQL:

SP_EXECUTESQL 是以
SQL 2005挨引入的新的系存储过程,也是用来拍卖动态SQL 语句之。它比EXEC
更加灵活,首先为行一下率先不良的拼凑SQL语句:

SQL Server 18SQL Server 19

DECLARE @TableName NVARCHAR(50),@Sql NVARCHAR(MAX),@Score INT;
SET @TableName = 't_Student';
SET @Score = 90;
SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) +'WHERE Score >= '+CAST(@Score AS NVARCHAR(10))
EXEC SP_EXECUTESQL @sql --注意这里没有了()

View Code

 执行结果:

SQL Server 20

SP_EXECUTESQL 支持内嵌参数:

先行来拘禁一下SP_EXECUTESQL的语法:

sp_executesql [ @stmt = ] stmt
[ 
    {, [@params=] N'@parameter_name data_type [ OUT | OUTPUT ][,...n]' } 
     {, [ @param1 = ] 'value1' [ ,...n ] }
]

说明:
[ @stmt = ] stmt 包含 Transact-SQL 语句或批处理的 Unicode 字符串。stmt 必须是 Unicode 常量或 Unicode 变量。不允许使用更复杂的 Unicode 表达式(例如使用 + 运算符连接两个字符串)。不允许使用字符常量。如果指定了 Unicode 常量,则必须使用 N 作为前缀。例如,Unicode 常量 N'sp_who' 是有效的,但是字符常量 'sp_who' 则无效。字符串的大小仅受可用数据库服务器内存限制。在 64 位服务器中,字符串大小限制为 2 GB,即 nvarchar(max) 的最大大小。stmt 中包含的每个参数在 @params 参数定义列表和参数值列表中均必须有对应项

[ @params = ] N'@parameter_namedata_type[ ,... n ] ' 包含 stmt 中嵌入的所有参数定义的字符串。字符串必须是 Unicode 常量或 Unicode 变量。每个参数定义由参数名称和数据类型组成。n 是表示附加参数定义的占位符。在 stmt 中指定的每个参数必须在 @params 中定义。如果 stmt 中的 Transact-SQL 语句或批处理不包含参数,则不需要 @params。该参数的默认值为 NULL。

[ @param1 = ] 'value1' 

参数字符串中定义之率先独参数的值。该值可以是 Unicode 常量,也堪是
Unicode 变量。必须也 stmt 中蕴藏的每个参数提供参数值。如果 stmt 中之
Transact-SQL 语句或批处理没有参数,则免需这些价值。

 

 

[ OUT | OUTPUT ]

指令参数是出口参数。除非是公共语言运行 (CLR)
过程,否则 textntext 和 image 参数都可用作 OUTPUT
参数。使用 OUTPUT 关键字的输出参数可以为游标占位符,CLR 过程除外。

 

 

 

n 附加参数值的占位符。这些值只能为常量或变量,不能是很复杂的表达式(例如函数)或使用运算符生成的表达式。

返回代码值 : 

0(成功)或非零(失败)

 

结果集:从生成 SQL 字符串的装有 SQL 语句返回结果集

 

圈不知情没有关联,通过例子就是会十分理解的,依旧还实施方的 SQL 语句:

 

SQL Server 21SQL Server 22

DECLARE @OUT_Nums INT,@IN_Score INT,@Sql NVARCHAR(MAX)
SET @IN_Score = 90
SET @sql = 'SELECT @Nums=COUNT(1) FROM t_student WHERE Score >= @Score'
EXEC SP_EXECUTESQL @sql,N'@Nums INT OUT,@Score INT',@OUT_Nums OUTPUT,@IN_Score
SELECT @OUT_Nums AS '人数'

View Code

  执行结果:

SQL Server 23

 

得小心的是:

1、要求动态Sql和动态Sql参数列表必须是NVARCHAR

2、动态Sql的参数列表与表面提供值的参数列表顺序必需一样

3、一旦采用了 ‘@name = value’ 形式之后,所有继续的参数就不能不盖 ‘@name =
value’ 的款型传递,比如:

 

SQL Server 24SQL Server 25

DECLARE @OUT_Nums INT,@IN_Score INT,@Sql NVARCHAR(MAX)
SET @IN_Score = 90
SET @sql = 'SELECT @Nums=COUNT(1) FROM t_student WHERE Score >= @Score'
EXEC SP_EXECUTESQL @stmt=@sql,@params=N'@Nums INT OUT,@Score INT',@Nums=@OUT_Nums OUTPUT,@Score=@IN_Score
SELECT @OUT_Nums AS '人数'

View Code

 

经过地方的例证已经十分鲜明的标志了,在实施动态SQL 语句之时候,EXEC 和
 SP_EXECUTESQL 的分了,来总结一下:

1、 性能:

法定描述:sp_executesql stmt 参数中之 Transact-SQL
语句或批处理在实施 sp_executesql 语句子时才编译。随后,将编译 stmt 中的情节,并拿那当作实践计划运行。该实施计划单独于名吧 sp_executesql 的批处理的实施计划。sp_executesql 批处理不能引用调用 sp_executesql 的批处理面临扬言的变量。sp_executesql 批处理着的地面游标或变量对调用sp_executesql 的批判处理是不可见的。对数据库上下文所开的改观但以 sp_executesql 语句子结束前中。如果仅仅改变了晓句被之参数值,则 sp_executesql 可用来替存储过程往往执行
Transact-SQL 语句。因为 Transact-SQL
语句我保持无转换,仅参数值发生变化,所以 SQL Server
查询优化器可能重复使用首潮实施时所大成的行计划。

说通俗一点就是:如果用 EXEC 执行同样漫漫动态 SQL
语句,由于每次传入的参数不同等,所以每次变更的 @sql
就未均等,这样各个执行同样次SQL SERVER 就必须再次将执行的动态 Sql
重新编译一破
。但是SP_EXECUTESQL 则非平等,由于将数值参数化,要实行的动态 Sql
永远不会见转变,只是传入的参数的价值当变更,那每次执行的时刻即便不要再行编译,速度以及频率自然有升级。

2、从上面的例证我们就能够见到 SP_EXECUTESQL 命令比 EXEC
命令还灵活,因为它提供一个接口,该接口和支持输入参数为支撑出口参数。

3、EXEC
执行纯动态SQL,执行时或许无法使预编译的尽计划,关键是不安全,可以招
SQL 注入 ,而 SP_EXECUTESQL 执行参数化动态 SQL
,执行时会利用预编译的实施计划,而且保存存储过程不时虽得确定好动用的预编译的行计划,而且最好紧要的凡“安全”,天然免疫SQL
注入

 

摘自:http://www.cnblogs.com/lxblog/archive/2013/01/14/2859828.html

 

 

相关文章