首页 > 运维 > linux运维 > 正文

exec 与 exec sp_executesql 的用法及比较[通俗易懂]

爱谁谁
发布: 2025-07-09 12:24:31
原创
556人浏览过

大家好,又见面了,我是你们的朋友全栈君。

本文将详细介绍如何使用 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
登录后复制
  1. 带有输入参数时的使用
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。

  1. 带有输入参数时的使用
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
登录后复制
  1. 带有输入输出参数时的使用
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
登录后复制
  1. 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 比较

  1. exec 与 exec sp_executesql 都可以用于执行动态SQL。

  2. 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
登录后复制
  1. sp_executesql 要求动态Sql和动态Sql参数列表必须是Nvarchar, 动态Sql的参数列表与外部提供值的参数列表顺序必需一致,且不能使用变量。

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

  3. 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 的用法及比较[通俗易懂]

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

下面,来看一下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 的用法及比较[通俗易懂]

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

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

以上就是exec 与 exec sp_executesql 的用法及比较[通俗易懂]的详细内容,更多请关注php中文网其它相关文章!

最佳 Windows 性能的顶级免费优化软件
最佳 Windows 性能的顶级免费优化软件

每个人都需要一台速度更快、更稳定的 PC。随着时间的推移,垃圾文件、旧注册表数据和不必要的后台进程会占用资源并降低性能。幸运的是,许多工具可以让 Windows 保持平稳运行。

下载
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn
最新问题
开源免费商场系统广告
热门教程
更多>
最新下载
更多>
网站特效
网站源码
网站素材
前端模板
关于我们 免责申明 意见反馈 讲师合作 广告合作 最新更新
php中文网:公益在线php培训,帮助PHP学习者快速成长!
关注服务号 技术交流群
PHP中文网订阅号
每天精选资源文章推送
PHP中文网APP
随时随地碎片化学习
PHP中文网抖音号
发现有趣的

Copyright 2014-2025 https://www.php.cn/ All Rights Reserved | php.cn | 湘ICP备2023035733号