exec 与 exec sp_executesql 的用法及比较
来源:互联网 发布:python爬虫开发 编辑:程序博客网 时间:2024/05/22 18:22
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执行sqlset @sql = 'select * from Person.Person where FirstName =''' + @FName2 + ''' and PersonType= ''' + @PeronType + ''''exec(@sql)-- 推荐这样使用(可以防止SQL注入,可以重用执行计划)-- 此处输入参数要加上N,不然会报错:过程需要类型为 'ntext/nchar/nvarchar' 的参数 '@paramsset @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 -- -1set @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 @tmpexec sp_executesql N'select top 10 BusinessEntityID, FirstName, LastName from Person.Person'insert into @tmpexec(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 sp_executesql
DBCC FREEPROCCACHEDECLARE @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', @IDSELECT cacheobjtype,objtype,usecounts,sql FROM sys.syscacheobjects WHERE sql NOT LIKE '%cach%' AND sql NOT LIKE '%sys.%'同样执行三次之后,查询到的执行计划缓存如下
通过上面的截图可以看到,只生成了一次执行计划。
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
阅读全文
0 0
- exec 与 exec sp_executesql 的用法及比较
- exec与 exec "sp_executesql" 的用法
- EXEC与sp_executesql的区别及应用
- exec与sp_executesql的区别
- SQL:exec sp_executesql 用法
- SQL:exec sp_executesql 用法
- exec与sp_executesql
- [转载]sp_executesql 与 exec
- exec与sp_executesql
- 求解exec sp_executesql的用法讲解.
- SQLSERVER的自定义函数,不支持使用exec sp_executesql与Exec()
- exec与sp_executesql语法的区别详解
- exec与sp_executesql语法的区别详解
- EXEC sp_executesql
- EXEC sp_executesql
- mssql:sp_executesql与exec(@sql)
- 動態語句的使用方法(exec/sp_executesql)
- EXEC和sp_executesql的区别
- 小白的机器学习:Numpy矩阵扫盲
- FZU
- SSH 环境搭建及注解实现登录功能 DEMO
- 同一地方组建多个 ZigBee 网络
- 使用jsp javabean 和jdbc实现一个个人信息提交页面
- exec 与 exec sp_executesql 的用法及比较
- 序列化和反序列化
- php微信自定义分享
- let 22 generate parentheses
- IT行业风投
- HTTP (2) 状态码
- java.lang.NoSuchMethodError: javax.servlet.http.HttpServletResponse.getHeaders(Ljava/lang/String;)Lj
- 泛刷水题记17.12.22
- spring cloud eureka