sp_executesql命令比EXEC命令更灵活,因为它提供一个接口,该接口及支持输入参数也支持输出参数。 这功能使你可以创建带参数的查询字符串,这样就可以比EXEC更好的重用执行计划。 sp_executesql的构成与存储过程非常相似,不同之处在于你是动态构建代码。 它的构成包括:代码块,参数声明部分,参数赋值部分 语法:
EXEC sp_executesql
@stmt = <statement>,--类似存储过程主体
@params = <params>, --类似存储过程参数部分
<params assignment> --类似存储过程调用(参数赋值)
@stmt参数是输入的动态批处理,它可以引入输入参数或输出参数,和存储过程的主体语句一样,只不过它是动态的,而存储过程是静态的,不过你也可以在存储过程中使用sp_executesql;
@params参数与定义输入/输出参数的存储过程头类似,实际上和存储过程头的语法完全一样;
@<params assignment> 与调用存储过程的EXEC部分类似。
*/
--EXEC不提供接口 --这里的接口是指,它不能执行一个包含一个带变量符的批处理。如下: DECLARE @TableName VARCHAR(50),@Sql NVARCHAR(MAX),@OrderID INT; SET @TableName = 'Orders'; SET @OrderID = 10251; SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) +'WHERE OrderID = @OrderID ORDER BY ORDERID DESC'
EXEC(@sql); --编译通过,执行时提示:必须声明标量变量 "@OrderID"。 EXEC sp_executesql @sql; --更换后可行
--例如,下面的静态代码简单的演示了如何从动态批处理中利用输出参数@p把值返回到外部批处理中的变量@i. DECLARE @sql AS NVARCHAR(12),@i AS INT; SET @sql = N' SET @p = 10'; EXEC sp_executesql @stmt = @sql, @params = N'@p AS INT OUTPUT', @p = @i OUTPUT SELECT @i--该代码返回输出10
DBCC FREEPROCCACHE --清空缓存中的执行计划
SELECT cacheobjtype,objtype,usecounts,sql FROM sys.syscacheobjects WHERE sql NOT LIKE '%cache%' AND sql NOT LIKE '%sys.%' AND sql NOT LIKE '%sp_executesql%'
总结:sp_executesql执行计划会被缓存,而execute不可以,如果大量重复查询,sp_executesql比execute更能提高数据库性能。