9.7 SQL Server动态SQL
SQL Server动态SQL
简介
动态SQL是一种可以在运行时动态构造SQL语句的编程技术。因此可以创建更通用、更灵活的SQL语句，因为SQL语句的全文可能在编译时未知。例如，您可以使用动态SQL创建一个存储过程，该存储过程查询一个直到运行时才知道其名称的表的数据。或者是分页存储过程等。
创建动态SQL很简单，只需将其设置字符串，比如：
'SELECT * FROM production.products';
要执行动态SQL语句，请调用存储过程`sp_executesql`，如以下语句所示：
EXEC sp_executesql N'SELECT * FROM production.products';
因为sp_executesql接受Unicode字符串的动态SQL，所以需要加上前缀N。
虽然这个动态SQL不是很有用，但它很好地说明了动态SQL。
使用动态SQL查询任何表
首先，声明两个变量，@table用于保存要查询的表的名称，@sql用于保存动态sql。
DECLARE
@table NVARCHAR(128),
@sql NVARCHAR(MAX);
其次，将@table变量的值设置为production.products。
SET @table = N'production.products';
第三，通过将SELECT语句与表名参数连接起来，构造动态SQL：
SET @sql = N'SELECT * FROM ' + @table;
第四，通过传递@sql参数来调用sp_executesql存储过程。
EXEC sp_executesql @sql;
完整代码：
DECLARE
@table NVARCHAR(128),
@sql NVARCHAR(MAX);
SET @table = N'production.products';
SET @sql = N'SELECT * FROM ' + @table;
EXEC sp_executesql @sql;
上面的代码块与如下SQL语句返回同样的结果集：
SELECT * FROM production.products;
此时如果要从另一个表中查询数据，只需更改@table变量的值。
然而，如果我们将上述T-SQL块包装在存储过程中，则更实用。
SQL Server动态SQL与存储过程
此存储过程接受任何表，并使用动态SQL从指定表返回结果集：
CREATE PROC usp_query (
@table NVARCHAR(128)
)
AS
BEGIN
DECLARE @sql NVARCHAR(MAX);
-- 构造SQL
SET @sql = N'SELECT * FROM ' + @table;
-- 执行SQL
EXEC sp_executesql @sql;
END;
调用这个存储过程返回表production.brands表的所有数据
EXEC usp_query 'production.brands';
若要此存储过程按指定列排序并从表中返回前N行：
稍加修改：
CREATE OR ALTER PROC usp_query_topn(
@table NVARCHAR(128),
@topN INT,
@byColumn NVARCHAR(128)
)
AS
BEGIN
DECLARE
@sql NVARCHAR(MAX),
@topNStr NVARCHAR(MAX);
SET @topNStr = CAST(@topN as nvarchar(max));
-- construct SQL
SET @sql = N'SELECT TOP ' + @topNStr +
' * FROM ' + @table +
' ORDER BY ' + @byColumn + ' DESC';
-- execute the SQL
EXEC sp_executesql @sql;
END;
例如，从production.products中获得前10种最贵的产品：
EXEC usp_query_topn
'production.products',
10,
'list_price';
返回库存量最高的前10种产品：
EXEC usp_query_topn
'production.tocks',
10,
'quantity';
SQL Server动态SQL与SQL注入
创建一张测试表：
CREATE TABLE tests(id INT);
如下语句返回 production.brands 表中的所有行:
EXEC usp_query 'production.brands';
但是如果用户按如下方式传递表名：
EXEC usp_query 'production.brands;DROP TABLE sales.tests';
这种技术称为SQL注入。一旦执行该语句，则sales.tests表被删除，因为存储过程usp_query会执行这两个语句：
SELECT * FROM production.brands;DROP TABLE sales.tests
为了防止这种SQL注入，可以使用QUOTENAME()函数将架构(schema)和表名分离，如以下查询所示：
CREATE OR ALTER PROC usp_query
(
@schema NVARCHAR(128),
@table NVARCHAR(128)
)
AS
BEGIN
DECLARE
@sql NVARCHAR(MAX);
-- construct SQL
SET @sql = N'SELECT * FROM '
+ QUOTENAME(@schema)
+ '.'
+ QUOTENAME(@table);
-- execute the SQL
EXEC sp_executesql @sql;
END;
现在，如果将架构和表名传递给存储过程：
EXEC usp_query 'production','brands';
但是，如果尝试注入其他语句，例如：
EXEC usp_query
'production',
'brands;DROP TABLE sales.tests';
会报如下错误：
Invalid object name 'production.brands;DROP TABLE sales.tests'.
有关sp_executesql存储过程的更多信息
语法：
EXEC sp_executesql
sql_statement
parameter_definition
@param1 = value1,
@param2 = value2,
...
sql_statement是包含T-SQL语句的Unicode字符串。sql_statement可以包含参数，比如：SELECT * FROM table_name WHERE id=@id中的@id就是参数。parameter_definition是一个字符串，包含sql语句中嵌入的所有参数的定义。每个参数定义都包含一个参数名及其数据类型，例如@id INT。参数定义用逗号(,)分隔。@param1 = value1, @param2 = value2,…为parameter_definition字符串中定义的每个参数指定一个值。
示例:使用sp_executesql存储过程查找标价大于100且类别为1的产品：
EXEC sp_executesql
N'SELECT *
FROM
production.products
WHERE
list_price> @listPrice AND
category_id = @categoryId
ORDER BY
list_price DESC',
N'@listPrice DECIMAL(10,2),
@categoryId INT'
,@listPrice = 100
,@categoryId = 1;
