在SQL Server中生成动态SQL语句

核心提示： 笔者在此设置@WhereClause允许NULL值，因为我们可能并不总是想为@WhereClause传递一个值，在SQL Server中生成动态SQL语句(3)，对这个存储过程的每次执行而言，每一个字段都从SalesHistory中返回前五行，需谨慎对待，因为这种代码可以将一些潜在的漏洞引

笔者在此设置@WhereClause允许NULL值，因为我们可能并不总是想为@WhereClause传递一个值。

对这个存储过程的每次执行而言，每一个字段都从SalesHistory中返回前五行。如果为@WhereClause参数传递了一个值，执行语句将把此字符串添加到@SelectStatement字符串中。然后笔者使用了存储过程sp_executesql执行动态生成的SQL字符串。

sp_executesql或 EXECUTE()

在SQL Server中有两种方法执行动态SQL语句，一是使用sp_executesql系统存储过程，二是使用EXECUTE()。有时这两种方法可以产同样的结果，不过在其如何运行上却有着一些不同点。

系统存储过程sp_executesql允许参数可被传递进入或传出动态的SQL语句，而EXECUTE()则不然。因为SQL语句是作为一个参数被传递给sp_executesql存储过程中的，与EXECUTE()相比，它不易受到SQL注入式攻击。因为sp_executesql是一个存储过程，所以将SQL字符串传递给它可以使SQL字符串有更多的机会被放置在高速缓存中。以笔者的观点，sp_executesql可以生成清晰而且容易阅读和维护的代码。这就是笔者为什么更喜欢用sp_executesql来执行动态SQL语句的原因。

在笔者前面的例子中，我们看了如何通过将一个WHERE子句传递给一个存储过程而生成一个简单的SQL语句。不过，如果我们想从动态生成的SQL语句中得到参数值的列表该怎么办?笔者将使用sp_executesql，因为它准许我们输入和输出参数。

我们要稍微修改一下最初的存储过程，这就可以将从SQL语句中返回的记录总数分配给一个输出参数。

　　DROPPROCEDUREusp_GetSalesHistory
　　GO
　　CREATEPROCEDUREusp_GetSalesHistory
　　(
　　@WhereClauseNVARCHAR(2000)=NULL,
　　@TotalRowsReturnedINTOUTPUT
　　)
　　AS
　　BEGIN
　　DECLARE@SelectStatementNVARCHAR(2000)
　　DECLARE@FullStatementNVARCHAR(4000)
　　DECLARE@ParameterListNVARCHAR(500)
　　SET@ParameterList='@TotalRowsReturnedINTOUTPUT'
　　SET@SelectStatement='SELECT@TotalRowsReturned=COUNT(*)FROMSalesHistory'
　　SET@FullStatement=@SelectStatement+ISNULL(@WhereClause,'')
　　PRINT@FullStatement
　　EXECUTEsp_executesql@FullStatement,@ParameterList,@TotalRowsReturned=@TotalRowsReturnedOUTPUT
　　END
　　GO

在上面过程中，笔者需要声明一个参数列表，以传递给sp_executesql存储过程，因为在运行时将一个值分配给了变量。对sp_executesql调用的唯一一个变化是在usp_GetSalesHistory存储过程中，笔者将从调用中得到的输出参数分配给了本地的@TotalRowsReturned参数。

我们还可以用与以前类似的方式调用usp_GetSalesHistory存储过程，不过在此增加了一个输出参数，用以指明返回的行。

　　DECLARE@WhereClauseNVARCHAR(2000),@TotalRowsReturnedINT
　　SET@WhereClause='WHEREProduct=''Computer'''
　　EXECUTEusp_GetSalesHistory
　　@WhereClause=@WhereClause,
　　@TotalRowsReturned=@TotalRowsReturnedOUTPUT
　　SELECT@TotalRowsReturned

小心为妙

虽然笔者并不极力推荐动态SQL语句，但它确实是一个有用的工具。如果你决定要将动态SQL语句集成到实际的代码中，需谨慎对待。因为这种代码可以将一些潜在的漏洞引入到你的系统中;如果你认真对待了，这种代码可以灵活为你地解决一些问题。