大家好，又见面了，我是你们的朋友全栈君。
本文将详细介绍如何使用 exec 和 exec sp_executesql 来执行动态SQL，并对它们进行比较。
（下面用到的数据库表来自sqlserver 的示例数据库 AdventureWorks2008）
一、exec 与 exec sp_executesql 的用法
- 动态SQL（使用字符串拼接的方式）
declare @FName2 varchar(20) = 'Ken', @PeronType varchar(10) = 'GC', @sql nvarchar(1000); -- 不推荐这样使用 exec('select * from Person.Person where FirstName =''' + @FName2 + ''' and PersonType= ''' + @PeronType + '''') -- sp_executesql 不能接收含有变量拼接的sql字符串。下面的sql执行会报错 -- exec sp_executesql 'select * from Person.Person where FirstName =''' + @FName2 + ''' and PersonType= ''' + @PeronType + '''' -- 不推荐这样使用：无法防止sql注入,无法重用执行计划，拼接麻烦且容易出错（字符串类型的需要单引号括起来） set @sql = 'select * from Person.Person where FirstName =''' + @FName2 + ''' and PersonType= ''' + @PeronType + '''' exec sp_executesql @sql
- 带有输入参数时的使用
declare @FName2 varchar(20) = 'Ken', @PeronType varchar(10) = 'GC', @sql nvarchar(1000); -- 推荐先使用变量存放拼接的sql，再使用exec执行sql set @sql = 'select * from Person.Person where FirstName =''' + @FName2 + ''' and PersonType= ''' + @PeronType + '''' exec(@sql) -- 推荐这样使用(可以防止SQL注入，可以重用执行计划） -- 此处输入参数要加上N,不然会报错：过程需要类型为 'ntext/nchar/nvarchar' 的参数 '@params set @sql = 'select * from Person.Person where FirstName =@FName and PersonType=@PersonType' exec sp_executesql @sql, N'@FName varchar(20), @PersonType varchar(10)', @FName2, @PeronType
注：exec 只能使用拼接字符串的方式，不支持使用输入参数，而且执行计划不能重用。因此，一般情况下，推荐使用 exec sp_executesql 的方式，而不是 exec。
- 带有输入参数时的使用
declare @sql nvarchar(1000), @cnt int = -1; -- 使用 exec -- exec sql内无法访问sql之外定义的变量，直接使用下面的会报错： 必须声明变量 "@cnt"。外部也无法访问到 exec sql里定义的变量 --无法直接将值传出，只能通过select 变量/insert into exec等方式看到值 --exec('select @cnt=count(1) from Person.Person; select @cnt') exec('declare @cnt int; select @cnt=count(1) from Person.Person') print @cnt -- -1, 无法访问 exec 里取到的 @cnt 的值 set @sql = 'select @cnt=count(1) from Person.Person' exec sp_executesql @sql, N'@cnt int output', @cnt output --此处必须加上ouput，不然无法取到值 print @cnt
- 带有输入输出参数时的使用
declare @sql nvarchar(1000), @cnt int = -1, @FName varchar(20) = 'Ken'; exec('declare @cnt int; select @cnt=count(1) from Person.Person where FirstName = ''' + @FName + '''; select @cnt') print @cnt -- -1 set @sql = 'select @cnt=count(1) from Person.Person where FirstName = @FName' exec sp_executesql @sql, N'@cnt int output, @FName varchar(20)', @cnt output, @FName --此处必须加上ouput，不然无法取到值 print @cnt
- insert into exec/exec sp_executesql 的使用
declare @tmp table (BusinessEntityID int, FirstName varchar(50), LastName varchar(50)) insert into @tmp exec sp_executesql N'select top 10 BusinessEntityID, FirstName, LastName from Person.Person' insert into @tmp exec(N'select top 10 BusinessEntityID, FirstName, LastName from Person.Person') select * from @tmp
二、exec 与 exec sp_executesql 比较
-
exec 与 exec sp_executesql 都可以用于执行动态SQL。
-
sp_executesql 后面需要直接使用表示拼接后的sql的变量或者sql常量字符串，后面不能直接使用常量+变量拼接的语句。如下面的语句会报错：
declare @FName2 varchar(20) = 'Ken', @PeronType varchar(10) = 'GC', @sql nvarchar(1000); exec sp_executesql 'select * from Person.Person where FirstName =''' + @FName2 + ''' and PersonType= ''' + @PeronType + ''''
这种情况下，需要先将sql拼凑后的结果放入一个变量中，然后使用 exec sp_executesql 执行；或者使用入参的方式来实现。推荐使用下面的方式：
declare @FName2 varchar(20) = 'Ken', @PeronType varchar(10) = 'GC', @sql nvarchar(1000); set @sql = 'select * from Person.Person where FirstName = @FName2 and PersonType = @PeronType' exec sp_executesql @sql, N'@FName varchar(20), @PersonType varchar(10)', @FName2, @PeronType
-
sp_executesql 要求动态Sql和动态Sql参数列表必须是Nvarchar, 动态Sql的参数列表与外部提供值的参数列表顺序必需一致，且不能使用变量。
-
exec 查询不能使用sql外面定义的变量，查询的结果也不容易进行使用。而exec sp_executesql 可以使用入参和出参的方式很方便的获取或者返回内容。
-
sp_executesql 可以建立带参数的查询字符串还可以重用执行计划。通过下面的示例来了解一下。
首先是 exec：
DBCC FREEPROCCACHE -- 清空执行计划缓存 DECLARE @Sql NVARCHAR(MAX), @ID INT; SET @ID = 15; -- 15使用之后，换成10， 12等再次执行 SET @sql = 'SELECT * FROM Person.Person WHERE BusinessEntityID = '+CAST(@ID AS VARCHAR(10))+' ORDER BY BusinessEntityID DESC' EXEC(@sql); SELECT cacheobjtype,objtype,usecounts,sql FROM sys.syscacheobjects WHERE sql NOT LIKE '%cach%' AND sql NOT LIKE '%sys.%'
使用exec 执行三次后，查询到的执行计划缓存如下：
![exec 与 exec sp_executesql 的用法及比较[通俗易懂]](https://img.php.cn/upload/article/001/503/042/175203507439048.jpg "exec 与 exec sp_executesql 的用法及比较[通俗易懂]")
通过上面的截图可以看到，执行三次生成了三次执行计划。
下面，来看一下exec sp_executesql：
DBCC FREEPROCCACHE DECLARE @Sql NVARCHAR(MAX), @ID INT; SET @ID = 17; SET @sql = 'SELECT * FROM Person.Person WHERE BusinessEntityID = @ID ORDER BY BusinessEntityID DESC' exec sp_executesql @sql, N'@ID int', @ID SELECT cacheobjtype,objtype,usecounts,sql FROM sys.syscacheobjects WHERE sql NOT LIKE '%cach%' AND sql NOT LIKE '%sys.%'
同样执行三次之后，查询到的执行计划缓存如下：
![exec 与 exec sp_executesql 的用法及比较[通俗易懂]](https://img.php.cn/upload/article/001/503/042/175203507420099.jpg "exec 与 exec sp_executesql 的用法及比较[通俗易懂]")
通过上面的截图可以看到，只生成了一次执行计划。
- sp_executesql 可以建立带参数的查询字符串可以防止sql注入：
-- 下面的SQL注入 DECLARE @Sql NVARCHAR(MAX), @FName varchar(20); SET @FName = '''ken'' or 1=1'; SET @sql = 'SELECT * FROM Person.Person WHERE FirstName = ' + @FName + ' ORDER BY BusinessEntityID DESC' exec sp_executesql @sql --下面的可以防止SQL注入 DECLARE @Sql NVARCHAR(MAX), @FName varchar(20); SET @FName = '''ken'' or 1=1'; SET @sql = 'SELECT * FROM Person.Person WHERE FirstName = @FName ORDER BY BusinessEntityID DESC' exec sp_executesql @sql, N'@FName varchar(20)', @FName
发布者：全栈程序员栈长，转载请注明出处：https://www.php.cn/link/3cdc0a294d55c26d6d656877c731d24d 原文链接：https://www.php.cn/link/c8377ad2a50fb65de28b11cfc628d75c
© 版权声明
文章版权归作者所有，未经允许请勿转载。
THE END
