1. 全栈程序员必看首页

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

全栈程序员-站长 • • 未分类 • 阅读 46

exec 与 exec sp_executesql 的用法及比较[通俗易懂]exec与exec sp_executesql 都可以用于执行动态sql。下面先介绍它们的用法，然后再对它们进行比较（下面用到的数据库表来自SQLSERVER的示例数据库AdventureWorks2008）一、exec与exec sp_executesql 用法1.动态sql（使用字符串拼接的方式）declare@FName2varchar(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执行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。

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

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

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 与 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 的用法及比较[通俗易懂]
   通过上面的截图可以看到，只生成了一次执行计划。


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
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请联系我们举报，一经查实，本站将立刻删除。

发布者：全栈程序员-站长，转载请注明出处：https://javaforall.net/143351.html原文链接：https://javaforall.net

赞 (0)
0 0

关于作者

全栈程序员-站长的头像

全栈程序员-站长

133.5K 文章
3 粉丝
本网站汇聚当前互联网主流语音，持续更新，欢迎关注公众号“全栈程序员社区”
上一篇 2022年5月21日 下午9:00
下一篇 2022年5月21日 下午9:20


相关推荐

  • 回归分析中自变量取舍、检验及多重共线性处理（VIF）「建议收藏」

    回归分析中自变量取舍、检验及多重共线性处理（VIF）「建议收藏」

    回归分析中自变量取舍、检验及多重共线性处理（VIF）「建议收藏」A1正交假定：误差项矩阵与X中每一个x向量都不相关高斯-马尔科夫定理：若满足A1和A2假定，则采用最小二乘法得到回归参数估计是最佳线性无偏估计方程估计值b1和b2可以看做偏回归系数，也是相应自变量对y的一种偏效应偏效应：在控制变量下，各自变量X对因变量Y的净效应残差项：针对具体模型而言，被定义为样本回归模型中观测值与预测值之差误差项：针对总体真实回归模型而言，它由一些不可观测因素或测量…

    全栈程序员-站长的头像 全栈程序员-站长
    2022年5月30日
    68
  • Dashboard 页面无法加载 ​ openclaw

    Dashboard 页面无法加载 ​

    Dashboard 页面无法加载 ​

    全栈程序员-站长的头像 全栈程序员-站长
    2026年3月15日
    3
  • 随机梯度下降算法_梯度下降算法的正确步骤

    随机梯度下降算法_梯度下降算法的正确步骤

    随机梯度下降算法_梯度下降算法的正确步骤主要内容：提供不同算法的原理以及效果直观展示，并希望读者能够在实际问题中更合理的选用梯度下降类算法。目录：1.简介梯度下降法2.随机梯度下降3.随机梯度下降的问题与挑战4.随机梯度下降的优化算法（主要内容）

    全栈程序员-站长的头像 全栈程序员-站长
    2025年10月23日
    4
  • actionchains selenium_selenium中ActionChains常用的方法

    actionchains selenium_selenium中ActionChains常用的方法

    actionchains selenium_selenium中ActionChains常用的方法action ActionChains self driver click on element None 单击鼠标左键 click and hold on element None 点击鼠标左键 不松开 context click on element None 点击鼠标右键 double click on element None 双击鼠标左键 drag and drop

    全栈程序员-站长的头像 全栈程序员-站长
    2026年3月17日
    2
  • 回文字符串判断python实现

    回文字符串判断python实现

    回文字符串判断python实现一：什么是回文字符串例如：abccba，qwerewq等，奇数偶数个都可以；二：实现方法1）：使用切片defis_palindromic(num):str_len=len(num)index=str_len//2str1=num[:index]str2=num[index:][::-1]ifstr1==s…

    全栈程序员-站长的头像 全栈程序员-站长
    2022年5月3日
    73
  • win10总显示打印机未连接服务器,win10安装打印机一直未响应。。。「建议收藏」

    win10总显示打印机未连接服务器,win10安装打印机一直未响应。。。「建议收藏」

    win10总显示打印机未连接服务器,win10安装打印机一直未响应。。。「建议收藏」Win10安装打印机驱动的方法1.首先将打印机与电脑进行连接，目前大部分打印机都是通过USB数据线与电脑U口进行连接的。在打印机连接完成后，我们需要通过以下方法查看打印机连接状态是否正常：2.从打开的“控制面板”界面中，点击“硬件和声音”栏目中的“查看设备和打印机”按钮进入。3.此时将打开“设备和打印机”窗口，从此界面中就可以找到“未指定”的设备，此设备便是当前所连接的打印机。4.Win10正式版…

    全栈程序员-站长的头像 全栈程序员-站长
    2022年6月6日
    87

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注

关注全栈程序员社区公众号