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

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

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/143351.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)
blank

相关推荐

  • 三级网络技术选择题知识点集绵

    三级网络技术选择题知识点集绵

  • 团队项目总结[通俗易懂]

    团队项目总结[通俗易懂]设想和目标1.我们的软件要解决什么问题?是否定义得很清楚?是否对典型用户和典型场景有清晰的描述?我们的软件要解决的就是让学生们可以自由的处理他们不想要但并未失去价值的东西;并且对典型用户和典型场景

  • 基于图同构网络(GIN)的图表征网络的实现

    基于图同构网络(GIN)的图表征网络的实现基于图同构网络的图表征学习主要包含以下两个过程:首先计算得到节点表征;其次对图上各个节点的表征做图池化(GraphPooling),或称为图读出(GraphReadout),得到图的表征(GraphRepresentation)。在这里,我们将采用自顶向下的方式,来学习基于图同构模型(GIN)的图表征学习方法。我们首先关注如何基于节点表征计算得到图的表征,而忽略计算结点表征的方法。基于图同构网络的图表征模块(GINGraphReprModu

  • 使用Ubuntu16.04搭建CMS服务器

    使用Ubuntu16.04搭建CMS服务器以此为笔记,方便后面查阅。以下是正文。注:使用root帐号或每条shell命令前加sudo.1. 安装Ubuntu16.04版本。2. 安装后,进入系统,打开终端,开始使用以下shell命令进行搭建。3.apt-getupdate4.apt-getupgrade-y5.添加ppa软件源:     1).apt-getinstall-ypython…

    2022年10月19日
  • 河南计算机职称考试模块选择,2016职称计算机考试模块Dreamweaver选择冲刺题1

    河南计算机职称考试模块选择,2016职称计算机考试模块Dreamweaver选择冲刺题1单选题1.网页的主体内容将写在什么标签内部:A.标签B.标签C.标签D.标签答案:A2.下面关于查看源代码说法正确的是:A.一般不能在IE中查看网页的源代码B.在DreamweaverMX中可以使用代码监视器(CodeInspector)查看的页面的源代码C.在DreamweaverMX中只有一种方法可以查看网页的源代码D.以上说法都错答案:B3.在Dreamweaver中,下面关于使…

  • 数据库中间件介绍_数据中间件

    数据库中间件介绍_数据中间件文章目录1、Cobar2、Mycat3、OneProxy4、kingshard5、Vitess6、Atlas7、MaxScale8、MySQLRoute9、ShardingSpere(Sharding-JDBC)10、DRDS1、CobarCobar属于阿进而B2B事业群,始于2008年,在阿里服役年多,接管3000+个MySql数据库的schema,集群日处理在线SQL请求50亿次以上。由于Cobar发起人离职,Cobar停止维护。2、Mycat开源社区在阿里Cobar基础上进行

    2022年10月28日

发表回复

您的电子邮箱地址不会被公开。

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