exec 与 exec sp_executesql 的用法及比较

概述exec 与 exec sp_executesql  都可以用于执行动态sql。下面先介绍它们的用法，然后再对它们进行比较 （下面用到的数据库表来自SQLSERVER 的示例数据库 AdventureWorks2008） 一、exec 与 exec sp_executesql  用法 1. 动态sql（使用字符串拼接的方式） declare @FName2 varchar(20) = 'Ken',

exec 与 exec sp_executesql  都可以用于执行动态sql。下面先介绍它们的用法，然后再对它们进行比较

（下面用到的数据库表来自sqlSERVER 的示例数据库 AdventureWorks2008）

一、exec 与 exec sp_executesql  用法 1. 动态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

2. 带有输入参数时的使用

declare @Fname2 varchar(20) = 'Ken',@PeronType varchar(10) = 'GC',@sql nvarchar(1000);-- 推荐先使用变量存放拼接的sql，再使用exec执行sqlset @sql =  'select * from Person.Person where Firstname =''' + @Fname2 + ''' and PersonType= ''' + @PeronType + ''''exec(@sql)-- 推荐这样使用(可以防止sql注入，可以重用执行计划）-- 此处输入参数要加上N,不然会报错：过程需要类型为 'ntext/nchar/nvarchar' 的参数 '@paramsset @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。 3. 带有输入参数时的使用

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

4. 带有输入输出参数时的使用

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  -- -1set @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

5. insert into exec/exec sp_executesql 的使用

declare @tmp table (	BusinessEntityID int,Firstname varchar(50),Lastname varchar(50))insert into @tmpexec sp_executesql N'select top 10 BusinessEntityID,Firstname,Lastname from Person.Person'insert into @tmpexec(N'select top 10 BusinessEntityID,Lastname from Person.Person')select * from @tmp

二、exec 与 exec sp_executesql 比较 1. exec 与 exec sp_executesql 都可以用于执行动态sql
2. sp_executesql 后面需要直接使用表示拼接后的sql的变量或者sql常量字符串，后面不能直接使用常量+变量拼接的语句     如下面的语句会报错

declare @Fname2 varchar(20) = 'Ken',@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',@sql nvarchar(1000);set @sql = 'select * from Person.Person where Firstname = @Fname2 and PersonType = @PeronType'exec sp_executesql @sql,@PeronType

3. sp_executesql要求动态sql和动态sql参数列表必须是Nvarchar,动态sql的参数列表与外部提供值的参数列表顺序必需一致，且不能使用变量。
4. exec 查询不能使用sql外面定义的变量，查询的结果也不容易进行使用。而exec sp_executesql 可以使用入参和出参的方式很方便的获取或者返回内容。
5. 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 sp_executesql 

DBCC FREEPROCCACHEDECLARE @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',@IDSELECT cacheobjtype,sql FROM sys.syscacheobjects WHERE sql NOT liKE '%cach%' AND sql NOT liKE '%sys.%'

    同样执行三次之后，查询到的执行计划缓存如下     

   通过上面的截图可以看到，只生成了一次执行计划。

6. 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

总结

以上是内存溢出为你收集整理的exec 与 exec sp_executesql 的用法及比较全部内容，希望文章能够帮你解决exec 与 exec sp_executesql 的用法及比较所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。