大家好,又见面了,我是你们的朋友全栈君。
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
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 比较
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
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 执行三次后,查询到的执行计划缓存如下
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.%'
同样执行三次之后,查询到的执行计划缓存如下
-- 下面的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账号...