SQL Server执行动态SQL正确方式
SQL Server执行动态SQL的话 应该如何实现呢?下面就为您介绍SQL Server执行动态SQL两种正确方式 希望可以让您对SQL Server执行动态SQL有更深的了解
动态SQL:code that is executed dynamically 它一般是根据用户输入或外部条件动态组合的SQL语句块 动态SQL能灵活的发挥SQL强大的功能 方便的解决一些其它方法难以解决的问题 相信使用过动态SQL的人都能体会到它带来的便利 然而动态SQL有时候在执行性能(效率)上面不如静态SQL 而且使用不恰当 往往会在安全方面存在隐患(SQL 注入式攻击)
动态SQL可以通过EXECUTE 或SP_EXECUTESQL这两种方式来执行
EXECUTE
执行 Transact SQL 批中的命令字符串 字符串或执行下列模块之一:系统存储过程 用户定义存储过程 标量值用户定义函数或扩展存储过程 SQL Server 扩展了 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( );SET@GroupName = SuperAdmin ;
EXECUTE( SELECT * FROM Groups WHERE GroupName= + SUBSTRING(@GroupName ) + ); SUBSTRING 附近有语法错误
DECLARE @Sql VARCHAR( );
DECLARE @GroupName VARCHAR( );SET@GroupName = SuperAdmin ;
SET@Sql= SELECT * FROM Groups WHERE GroupName= + SUBSTRING(@GroupName ) +
PRINT @Sql;EXECUTE(@Sql);
Summary:EXECUTE 括号里面只能是字符串变量 字符串常量 或它们的连接组合 不能调用其它一些函数 存储过程等 如果要使用 则使用变量组合 如上所示
DECLARE @Sql VARCHAR( );
DECLARE @GroupName VARCHAR( );SET@GroupName = SuperAdmin ;
SET@Sql= SELECT * FROM Groups WHEREGroupName=@GroupName
PRINT @Sql;EXECUTE(@Sql); 出错:必须声明标量变量 “@GroupName” SET@Sql= SELECT * FROM Groups WHERE GroupName= + QUOTENAME(@GroupName )
EXECUTE(@Sql); 正确:
DECLARE @Sql NVARCHAR( );
DECLARE @GroupName NVARCHAR( );SET@GroupName = SuperAdmin ;
SET@Sql= SELECT * FROM Groups WHEREGroupName=@GroupName
PRINT @Sql;
EXEC SP_EXECUTESQL @Sql N @GroupNameNVARCHAR @GroupName
查询出来没有结果 没有声明参数长度
DECLARE @Sql NVARCHAR( );
DECLARE @GroupName NVARCHAR( );SET@GroupName = SuperAdmin ;
SET@Sql = SELECT * FROM Groups WHERE GroupName=@GroupName
PRINT @Sql;
EXEC SP_EXECUTESQL @Sql N @GroupName NVARCHAR( ) @GroupName
Summary:动态批处理不能访问定义在批处理里的局部变量 SP_EXECUTESQL 可以有输入输出参数 比EXECUTE灵活
下面我们来看看EXECUTE SP_EXECUTESQL的执行效率 首先把缓存清除执行计划 然后改变用@GroupName值SuperAdmin CommonUser CommonAdmin分别执行三次 然后看看其使用缓存的信息
DBCC FREEPROCCACHE;
DECLARE @Sql VARCHAR( );
DECLARE @GroupName VARCHAR( );SET@GroupName = SuperAdmin ; CommonUser CommonAdmin
SET@Sql = SELECT * FROM Groups WHERE GroupName= + QUOTENAME(@GroupName )
EXECUTE(@Sql); SELECTcacheobjtype objtype usecounts sql
FROM sys syscacheobjects
WHERE sql NOTLIKE %cache%
ANDsql NOTLIKE %sys % ;
依葫芦画瓢 接着我们看看SP_EXECUTESQL的执行效率
DBCC FREEPROCCACHE;
DECLARE @Sql NVARCHAR( );
DECLARE @GroupName NVARCHAR( );SET@GroupName = SuperAdmin ; CommonUser CommonAdmin
SET@Sql = SELECT * FROM Groups WHERE GroupName=@GroupName
EXECUTESP_EXECUTESQL @Sql N @GroupName NVARCHAR( ) @GroupName;
SELECTcacheobjtype objtype usecounts sql
FROM sys syscacheobjects
WHERE sql NOTLIKE %cache%
ANDsql NOTLIKE %sys % ;
lishixinzhi/Article/program/SQLServer/201311/22279
