游客您好
第三方账号登陆
Lv.9 管理员
1号会员,2592活跃度,2017/05/26 加入学习
  • 1394发帖
  • 426主题
  • 10关注
  • 4粉丝
这个人很懒,什么也没有留下。
最新推荐更多

[SQL Server数据库] 比较exec 与 exec sp_executesql 的用法

[复制链接]
amecaton实名认证 QQ认证 发表于 2020-10-9 16:52:41 | 显示全部楼层 |阅读模式 打印 上一主题 下一主题
exec 与 exec sp_executesql  都可以用于执行动态sql。下面先介绍它们的用法,然后再对它们进行比较

(下面用到的数据库表来自SQLSERVER 的示例数据库 AdventureWorks2008)
一、exec 与 exec sp_executesql  用法
1. 动态sql(使用字符串拼接的方式)
  1. declare @FName2 varchar(20) = 'Ken',
  2.         @PeronType varchar(10) = 'GC',
  3.         @sql nvarchar(1000);
  4. --  不推荐这样使用
复制代码
  1. exec('select * from Person.Person where FirstName =''' + @FName2 + ''' and PersonType= ''' + @PeronType + '''')
  2. -- sp_executesql 不能接收含有变量拼接的sql字符串。下面的sql执行会报错
  3. -- exec sp_executesql 'select * from Person.Person where FirstName =''' + @FName2 + ''' and PersonType= ''' + @PeronType + ''''
  4. -- 不推荐这样使用:无法防止SQL注入,无法重用执行计划,拼接麻烦且容易出错(字符串类型的需要单引号括起来)
  5. set @sql =  'select * from Person.Person where FirstName =''' + @FName2 + ''' and PersonType= ''' + @PeronType + ''''
  6. exec sp_executesql @sql
复制代码
2. 带有输入参数时的使用
  1. declare @FName2 varchar(20) = 'Ken',
  2.         @PeronType varchar(10) = 'GC',
  3.         @sql nvarchar(1000);

  4. -- 推荐先使用变量存放拼接的sql,再使用exec执行sql
  5. set @sql =  'select * from Person.Person where FirstName =''' + @FName2 + ''' and PersonType= ''' + @PeronType + ''''
  6. exec(@sql)

  7. -- 推荐这样使用(可以防止SQL注入,可以重用执行计划)
  8. -- 此处输入参数要加上N,不然会报错:过程需要类型为 'ntext/nchar/nvarchar' 的参数 '@params
  9. set @sql = 'select * from Person.Person where FirstName =@FName and PersonType=@PersonType'
  10. exec sp_executesql @sql, N'@FName varchar(20), @PersonType varchar(10)', @FName2, @PeronType
复制代码
注:exec 只能使用拼接字符串的方式,不支持使用输入参数,而且执行计划不能重用。因此,一般情况下,
推荐使用 exec sp_executesql 的方式,而不是exec。
3. 带有输入参数时的使用
  1. declare @sql nvarchar(1000),
  2.         @cnt int = -1;


  3. -- 使用 exec
  4. -- exec sql内无法访问sql之外定义的变量,直接使用下面的会报错: 必须声明变量 "@cnt"。外部也无法访问到 exec sql里定义的变量
  5. --无法直接将值传出,只能通过select 变量/insert into exec等方式看到值
  6. --exec('select @cnt=count(1) from Person.Person; select @cnt')
  7. exec('declare @cnt int; select @cnt=count(1) from Person.Person')
  8. print @cnt  -- -1, 无法访问 exec 里取到的  @cnt 的值
  9. set @sql = 'select @cnt=count(1) from Person.Person'
  10. exec sp_executesql @sql, N'@cnt int output', @cnt output --此处必须加上ouput,不然无法取到值
  11. print @cnt
复制代码

4. 带有输入输出参数时的使用
  1. declare @sql nvarchar(1000),
  2.         @cnt int = -1,
  3.         @FName varchar(20) = 'Ken';


  4. exec('declare @cnt int; select @cnt=count(1) from Person.Person where FirstName = ''' + @FName + ''';  select @cnt')
  5. print @cnt  -- -1
  6. set @sql = 'select @cnt=count(1) from Person.Person where FirstName = @FName'
  7. exec sp_executesql @sql, N'@cnt int output, @FName varchar(20)', @cnt output, @FName --此处必须加上ouput,不然无法取到值
  8. print @cnt
复制代码

5. insert into exec/exec sp_executesql 的使用
  1. declare @tmp table (
  2.         BusinessEntityID int,
  3.         FirstName varchar(50),
  4.         LastName varchar(50)
  5. )

  6. insert into @tmp
  7. exec sp_executesql N'select top 10 BusinessEntityID, FirstName, LastName from Person.Person'

  8. insert into @tmp
  9. exec(N'select top 10 BusinessEntityID, FirstName, LastName from Person.Person')

  10. select * from @tmp
复制代码

二、exec 与 exec sp_executesql 比较 1. exec 与 exec sp_executesql 都可以用于执行动态sql

2. sp_executesql 后面需要直接使用表示拼接后的sql的变量或者sql常量字符串,后面不能直接使用常量+变量拼接的语句
     如下面的语句会报错

  1. declare @FName2 varchar(20) = 'Ken',
  2.         @PeronType varchar(10) = 'GC',
  3.         @sql nvarchar(1000);

  4. exec sp_executesql 'select * from Person.Person where FirstName =''' + @FName2 + ''' and PersonType= ''' + @PeronType + ''''
复制代码
这种情况下,需要先将sql拼凑后的结果放入一个变量中,然后使用 exec sp_executesql 执行;或者使用入参的方式来实现。推荐使用下面的方式

  1. declare @FName2 varchar(20) = 'Ken',
  2.         @PeronType varchar(10) = 'GC',
  3.         @sql nvarchar(1000);
  4. set @sql = 'select * from Person.Person where FirstName = @FName2 and PersonType = @PeronType'
  5. exec sp_executesql @sql, N'@FName varchar(20), @PersonType varchar(10)', @FName2, @PeronType
复制代码

3. sp_executesql要求动态Sql和动态Sql参数列表必须是Nvarchar, 动态Sql的参数列表与外部提供值的参数列表顺序必需一致,且不能使用变量。

4. exec 查询不能使用sql外面定义的变量,查询的结果也不容易进行使用。而exec sp_executesql 可以使用入参和出参的方式很方便的获取或者返回内容。

5. sp_executesql可以建立带参数的查询字符串还可以重用执行计划。
     通过下面的示例来了解一下
     首先是 exec

  1. DBCC FREEPROCCACHE  -- 清空执行计划缓存

  2. DECLARE @Sql NVARCHAR(MAX),@ID INT;
  3. SET @ID = 15; -- 15使用之后,换成10, 12等再次执行
  4. SET @sql = 'SELECT * FROM Person.Person WHERE BusinessEntityID = '+CAST(@ID AS VARCHAR(10))+' ORDER BY BusinessEntityID DESC'
  5. EXEC(@sql);

  6. SELECT cacheobjtype,objtype,usecounts,sql FROM sys.syscacheobjects WHERE sql NOT LIKE '%cach%' AND sql NOT LIKE '%sys.%'
复制代码
比较exec 与 exec sp_executesql 的用法,慧之家
  1. DBCC FREEPROCCACHE

  2. DECLARE @Sql NVARCHAR(MAX),@ID INT;
  3. SET @ID = 17;
  4. SET @sql = 'SELECT * FROM Person.Person WHERE BusinessEntityID = @ID ORDER BY BusinessEntityID DESC'
  5. exec sp_executesql @sql, N'@ID int', @ID

  6. SELECT cacheobjtype,objtype,usecounts,sql FROM sys.syscacheobjects WHERE sql NOT LIKE '%cach%' AND sql NOT LIKE '%sys.%'
复制代码

比较exec 与 exec sp_executesql 的用法,慧之家


  1. -- 下面的SQL注入
  2. DECLARE @Sql NVARCHAR(MAX),@FName varchar(20);
  3. SET @FName = '''ken'' or 1=1';
  4. SET @sql = 'SELECT * FROM Person.Person WHERE FirstName = ' + @FName + ' ORDER BY BusinessEntityID DESC'
  5. exec sp_executesql @sql

  6. --下面的可以防止SQL注入
  7. DECLARE @Sql NVARCHAR(MAX),@FName varchar(20);
  8. SET @FName = '''ken'' or 1=1';
  9. SET @sql = 'SELECT * FROM Person.Person WHERE FirstName = @FName ORDER BY BusinessEntityID DESC'
  10. exec sp_executesql @sql, N'@FName varchar(20)', @FName
复制代码











• 本内容来源互联网,如涉及版权问题请及时联系站长
• 为防止代码解析,除代码框内<>为正常,其余请重新替换<>尖括号

发布资源 快速回复 站点服务大厅 搜索 官方群