EXEC 和 SP_EXECUTESQL的区别

摘要:

MSSQL为我们提供了两种动态执行sql语句的命令:EXEC 和 SP_EXECUTESQL。通常SP_EXECUTESQL更具优势,因为它提供了输入输出的接口,且能够重用执行计划,大大提高执行效率,而且不会导致SQL注入,比较安全,这些优势都是EXEC所不具有的,EXEC通常用来执行存储过程。所以,除非有充分的理由,否则在执行动态SQL的时候尽量使用SP_EXECUTESQL。

用法举例:

-----------EXEC:

DECLARE @TableName VARCHAR(50),@Sql NVARCHAR(MAX),@OrderID INT;
SET @TableName = 'Orders';
SET @OrderID = 10251;
SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) +'WHERE OrderID = '+CAST(@OrderID AS VARCHAR(10))+' ORDER BY ORDERID DESC'
EXEC(@sql);

注:注意用EXEC的时候要有括号且括号中只允许有一个字符串变量,也可以是由多个字符串拼接而成的,例如:

EXEC(@sql+@sql2+@sql3);

但是如下就会报错。

EXEC('SELECT TOP('+ CAST(@TopCount AS VARCHAR(10)) +')* FROM '+QUOTENAME(@TableName) +' ORDER BY ORDERID DESC');

所以最佳实践是把所有代码构造到一个字符串变量中,然后EXEC(变量)。

-----------SP_EXECUTESQL:

先来看一下SP_EXECUTESQL的语法:

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

[ @stmt= ] statement

包含 Transact-SQL 语句或批处理的 Unicode 字符串。@stmt 必须是 Unicode 常量或 Unicode 变量。  不允许使用更复杂的 Unicode 表达式(例如使用 + 运算符连接两个字符串)。不允许使用字符常量。 如果指定了 Unicode 常量,则必须使用 N 作为前缀, 字符串的大小仅受可用数据库服务器内存限制, 在 64 位服务器中,字符串大小限制为 2 GB,即 nvarchar(max) 的最大大小。

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

[ @param1= ] 'value1'

参数字符串中定义的第一个参数的值。  该值可以是 Unicode 常量,也可以是 Unicode 变量。  必须为 @stmt 中包含的每个参数提供参数值。  如果 @stmt 中的 Transact-SQL 语句或批处理没有参数,则不需要这些值。

注意:参数传递可以选择'@name = value'形式或者直接写'value', 但是一旦前面使用了 '@name = value' 形式,所有后续的参数就必须以 '@name = value' 的形式传递。如果需要传出某个参数的值,需要在[ @param1= ] 'value1'后面加上OUTPUT。

举例如下:

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 '人数'

上一篇:mysql – ERROR 1100(HY000)第11084行:表’search_index’未使用LOCK TABLES锁定


下一篇:从数据文件中恢复mysql数据库