<b>详解SQL Server中的动态SQL概念</b>[MSSQL防范]

动态SQL:code that is executed dynamically.它普通是按照用户输入或外部条件动态组合的SQL语句块.动态SQL能机动的施展SQL强盛的功效、便利的办理一些别的办法难以办理的问题.相信利用过动态SQL的人都能领会到它带来的便利,但是动态SQL有时刻在履行性能(效率)上面不如静态SQL,并且利用不得当,常常会在安全方面存在隐患(SQL 注入式攻击).

动态SQL可以通过EXECUTE 或SP_EXECUTESQL这两种方法来履行.（来自MSDN）

EXECUTE 履行 Transact-SQL 批中的号令字符串、字符串或履行下列模块之一:系统存储历程、用户定义存储历程、标量值用户定义函数或扩大存储历程.SQL Server 2005 扩大了 EXECUTE 语句,以使其可用于向链接服务器发送传送号令.此外,还可以显式设置履行字符串或号令的上下文

SP_EXECUTESQL

履行可以多次反复利用或动态生成的 Transact-SQL 语句或批处理.Transact-SQL 语句或批处理可以包含嵌入参数.在批处理、名称作用域和数据库上下文方面,SP_EXECUTESQL 与 EXECUTE 的行为相同.SP_EXECUTESQL stmt 参数中的 Transact-SQL 语句或批处理在履行 SP_EXECUTESQL 语句时才编译.随后,将编译 stmt 中的内容,并将其作为履行筹划运行.该履行筹划独立于名为 SP_EXECUTESQL 的批处理的履行筹划.SP_EXECUTESQL 批处理不能引用调用 SP_EXECUTESQL 的批处理中声明的变量.SP_EXECUTESQL 批处理中的本地游标或变量对调用 SP_EXECUTESQL 的批处理是不可见的.对数据库上下文所作的更改只在 SP_EXECUTESQL 语句完毕前有效.

假如只更改了语句中的参数值,则 sp_executesql 可用来替换存储历程多次履行 Transact-SQL 语句.因为 Transact-SQL 语句本身保持不变,仅参数值发生改变,所以 SQL Server 查询优化器大概反复利用初次履行时所生成的履行筹划.

普通来说,我们举荐、优先利用SP_EXECUTESQL来履行动态SQL,一方面它越发机动、可以有输入输出参数、别的一方面,查询优化器更有大概反复利用履行筹划,提高履行效率.还有就是利用SP_EXECUTESQL能提高安全性;当然也不是说要完好丢弃EXECUTE,在特定场所下,EXECUTE比SP_EXECUTESQL更便利些,比方动态SQL字符串是VARCHAR范例、不是NVARCHAR范例.SP_EXECUTESQL 只能履行是Unicode的字符串或是可以隐式转换为ntext的常量或变量、而EXECUTE则两种范例的字符串都能履行.

下面我们来比较看看EXECUTE 和SP_EXECUTESQL的一些细节地方.

EXECUTE (N'SELECT * FROM Groups')      --履行成功  
EXECUTE ('SELECT * FROM Groups')       --履行成功  
 
SP_EXECUTESQL N'SELECT * FROM Groups'; --履行成功  
SP_EXECUTESQL 'SELECT * FROM Groups'   --履行出错 

Summary:EXECUTE 可以履行非Unicode或Unicode范例的字符串常量、变量.而SP_EXECUTESQL只能履行Unicode或可以隐式转换为ntext的字符串常量、变量.

DECLARE @GroupName VARCHAR(50);  
SET @GroupName = 'SuperAdmin';  
EXECUTE ('SELECT * FROM Groups WHERE 
GroupName=''' + SUBSTRING(@GroupName, 1,5) + ''''); --'SUBSTRING' 四周有语法错误.  
DECLARE @Sql VARCHAR(200);  
DECLARE @GroupName VARCHAR(50);  
SET @GroupName = 'SuperAdmin';  
SET @Sql = 'SELECT * FROM Groups 
WHERE GroupName=''' + SUBSTRING(@GroupName, 1,5) + '''' 
--PRINT @Sql;  
EXECUTE (@Sql); 

Summary:EXECUTE 括号里面只能是字符串变量、字符串常量、或它们的衔接组合,不能调用别的一些函数、存储历程等. 假如要利用,则利用变量组合,如上所示.

DECLARE @Sql VARCHAR(200);  
DECLARE @GroupName VARCHAR(50);  
SET @GroupName = 'SuperAdmin';  
SET @Sql = 'SELECT * FROM Groups WHERE GroupName=@GroupName' 
--PRINT @Sql;  
EXECUTE (@Sql);  --出错:必须声明标量变量 "@GroupName".  
SET @Sql = 'SELECT * 
FROM Groups WHERE GroupName=' + QUOTENAME(@GroupName, '''')  
EXECUTE (@Sql);  --精确:  
DECLARE @Sql NVARCHAR(200);  
DECLARE @GroupName NVARCHAR(50);  
SET @GroupName = 'SuperAdmin';  
SET @Sql = 'SELECT * FROM Groups WHERE GroupName=@GroupName' 
PRINT @Sql;  
EXEC SP_EXECUTESQL @Sql, N'@GroupName NVARCHAR',@GroupName  

查询出来没有后果,没有声明参数长度.

DECLARE @Sql NVARCHAR(200);  
DECLARE @GroupName NVARCHAR(50);  
SET @GroupName = 'SuperAdmin';  
SET @Sql = 'SELECT * FROM Groups WHERE GroupName=@GroupName' 
PRINT @Sql;  
EXEC SP_EXECUTESQL @Sql, N'@GroupName NVARCHAR(50)',@GroupName  

Summary:动态批处理不能拜候定义在批处理里的部分变量 . SP_EXECUTESQL 可以有输入输出参数,比EXECUTE机动.

下面我们来看看EXECUTE , SP_EXECUTESQL的履行效率,首先把缓存排除履行筹划,然后改变用@GroupName值SuperAdmin、CommonUser、CommonAdmin辨别履行三次.然后看看其利用缓存的信息

DBCC FREEPROCCACHE;   
 
DECLARE @Sql VARCHAR(200);  
DECLARE @GroupName VARCHAR(50);  
SET @GroupName = 'SuperAdmin'; --'CommonUser', 'CommonAdmin'  
SET @Sql = 'SELECT * 
FROM Groups WHERE GroupName=' + QUOTENAME(@GroupName, '''')  
EXECUTE (@Sql);   
SELECT cacheobjtype, objtype, usecounts, sql  
FROM sys.syscacheobjects  
WHERE sql NOT LIKE '%cache%' 
  AND sql NOT LIKE '%sys.%'; 

以下图所示

依葫芦画瓢,接着我们看看SP_EXECUTESQL的履行效率.

DBCC FREEPROCCACHE;   
DECLARE @Sql NVARCHAR(200);  
DECLARE @GroupName NVARCHAR(50);  
SET @GroupName = 'SuperAdmin'; --'CommonUser', 'CommonAdmin'  
SET @Sql = 'SELECT * FROM Groups WHERE GroupName=@GroupName' 
EXECUTE SP_EXECUTESQL @Sql, N'@GroupName NVARCHAR(50)', @GroupName;   
SELECT cacheobjtype, objtype, usecounts, sql  
FROM sys.syscacheobjects  
WHERE sql NOT LIKE '%cache%' 
  AND sql NOT LIKE '%sys.%'; 

履行后果以下图所示:

Summary:EXEC 生成了三个独立的 ad hoc 履行筹划,而用SP_EXECUTESQL只生成了一次履行筹划,反复利用了三次,试想假如一个库里面,有很多这样近似的动态SQL,并且频繁履行,假如采取SP_EXECUTESQL就可以提高性能. 

原文标题:SQL Server 动态SQL

链接:http://www.cnblogs.com/kerrycode/archive/2010/08/05/1792671.html