动态 SQL

SQL Server 允许用字符串来动态构造 T-SQL 代码的一个批处理，接着再执行这个批处理。这种功能称为动态SQL （dynamic SQL）。SQL Server提供了两种执行动态 SQL 的方法：使用 EXEC（EXECUTE 的缩写）命令和sp_executesql 存储过程。

动态SQL 可以用于以下几种用途：

♦ 自动化管理任务。例如，对于数据库实例中的每个数据库，查询其元数据，为其执行 BACKUP DATABASE 语句。

♦ 改善特定任务的性能。例如，构造参数化的特定查询，以重用以前缓存过的执行计划。

♦ 在对实际数据进行查询的基础上，构造代码元素。例如，当事先不知道在PIVOT运算符的IN子句中应该出现哪些元素时，动态构造PIVOT查询。

注意：当把用户的输入拼接为代码中的一部分时，要特别小心。黑客们经常会试图注入（inject ）你不想运行的代码。要防止SQL注入，最好的办法就是避免将用户输入拼接为代码的一部分（例如，通过使用参数）。但是如果你确实需要将用户的输入拼接为代码的一部分，务必对用户的输入进行彻底检查，看看有没有SQL注入的企图。

1. EXEC 命令

EXEC 命令是T-SQL中最早提供的一种用于执行动态SQL 的方法。EXEC 接受一个字符串作为在圆括号中输入的参数，执行字符串中包含的批处理代码。EXEC 命令的输入既支持普通字符，也支持Unicode字符。

EXEC(‘SELECT * FROM dbo.Categories‘);

2. sp_executesql 存储过程

sp_executesql 存储过程是继EXEC命令之后引入的另一种执行动态SQL 的方法。从sp_executesql的调用接口来说，使用这个存储过程更安全和更灵活，因为它支持输入和输出参数。

注意：与EXEC命令不同的是，sp_executesql 存储过程只支持使用 Unicode字符串作为其输入的批处理代码。

正因为在动态 SQL 代码中可以使用输入的输出参数，这样就有助于写出更安全和更有效的代码。从安全性的角度来说，在代码中出现的参数并不是代码的一部分，而只是表达式中的运算对象。所以，通过使用参数，就可以不必受SQL 注入的困扰了。

sp_executesql 存储过程在执行性能上要比EXEC命令更好，因为它的参数化有助于重用缓存过的执行计划。执行计划是SQL Server为查询生成的物理处理计划，包含了一组指令，说明要访问哪些对象、以什么顺序、使用哪个索引、如何访问它们、使用什么联接算法、等等。为了简化处理，如果要重用以前缓存过的执行计划，必须满足的条件之一就是查询字符串应该和缓存中已经存在的执行计划的查询字符串相同。所以，有效重用查询执行计划最好的方法就是使用带有参数的存储过程。这样一来，即使参数值发生了变化，可查询字符串仍然保持相同。但如果你出于自己的原因而决定使用特定的代码，而不使用存储过程，至少你仍然可以尽可能地使用参数。只不过如果使用sp_executesql则会增加重用执行计划的机会。

sp_executesql 存储过程有两个输入参数和一个参数赋值部分。在第一个参数@stmt中，需要指定包含想要运行的批处理代码的Unicode字符串。第二个参数@params是一个Unicode字符串，包含@stmt中所有输入和输出参数的声明。接着为输入和输出参数指定取值，各参数之间用逗号分隔。

EXEC sp_executesql @stmt=N‘SELECT orderid ,custid FROM dbo.Orders WHERE orderid=@orderid;‘,

　　　　　　　　　　@params=N‘@orderid AS INT‘,

　　　　　　　　　　@orderid=10246;

动态 SQL,古老的榕树,5-wow.com