在需要解決某個棘手的數據庫問題時，生成SQL語句可作為一種強大的工具，雖然我們在使用它時必須十分小心。本文將探討如何用這種功能來輕松地生成SQL語句。
動態SQL語句
一個動態的SQL語句是在執行時創建的，不同的條件生成不同的SQL語句。在我們需要決定運行時有哪些字段從SELECT語句返回時，在決定查詢的不同標准時，動態地創建這些語句是很有用處的。
這些SQL字符串不是為了語法分析以便於查找錯誤，因為它們是在運行時生成的，而且它們有可能將安全漏洞引入到你的數據庫中。此外，SQL字符串有可能成為一個調試上惡夢，這就是為什麼筆者並非動態生成SQL語句的一個癡迷者的原因。但在有些情況下，這種功能卻是很不錯的。
一個動態的例子
筆者經常回答的一個問題是“我如何將我的WHERE語句傳遞給一個存儲過程?”，而且經常看到類似於下面的情況，其TSQL語法是非法的。
DECLARE@WhereClauseNVARCHAR(2000)
SET@WhereClause='Prouct=''Computer'''
SELECT*FROMSalesHistoryWHERE@WhereClause
但情況並不如此簡單，有時，需要額外的標准，而且隨著數據表的逐漸增大，就需要越來越多的標准。這通常可以通過為不同的標准編寫不同的存儲過程而解決，不過有時每次執行的這種標准是如此迥然不同，以至於在一個存儲過程中包含所有的可能性可能成為一個沉重的負擔。雖然這些存儲過程可以用於考慮每一個可能的WHERE語句，(當然這要依賴於不同的參數) ，這通常會引起性能上的降低，因為在WHERE子句中有太多的條件。
讓我們看看如何創建一個簡單的動態查詢。首先，我們需要一個表和一些查詢的數據。下面的腳本創建了SalesHistory表並將數據裝載到其中。
CREATETABLE[dbo].[SalesHistory]
(
[SaleID][int]IDENTITY(1,1),
[Product][varchar](10)NULL,
[SaleDate][datetime]NULL,
[SalePrice][money]NULL
)
GO
SETNOCOUNTON
DECLARE@iINT
SET@i=1
WHILE(@i<=5000)
BEGIN
INSERTINTO[SalesHistory](Product,SaleDate,SalePrice)
VALUES('Computer',DATEADD(ww,@i,'3/11/1919'),
DATEPART(ms,GETDATE())+(@i+57))
INSERTINTO[SalesHistory](Product,SaleDate,SalePrice)
VALUES('BigScreen',DATEADD(ww,@i,'3/11/1927'),
DATEPART(ms,GETDATE())+(@i+13))
INSERTINTO[SalesHistory](Product,SaleDate,SalePrice)
VALUES('PoolTable',DATEADD(ww,@i,'3/11/1908'),
DATEPART(ms,GETDATE())+(@i+29))
SET@i=@i+1
END
下面我們創建一個可以接受WHERE子句的存儲過程。為了達成這個例子的目的，筆者將假定WHERE子句是從調用客戶應用程序中動態生成的。
CREATEPROCEDUREusp_GetSalesHistory
(
@WhereClauseNVARCHAR(2000)=NULL
)
AS
BEGIN
DECLARE@SelectStatementNVARCHAR(2000)
DECLARE@FullStatementNVARCHAR(4000)
SET@SelectStatement='SELECTTOP5*FROMSalesHistory'
SET@FullStatement=@SelectStatement+ISNULL(@WhereClause,'')
PRINT@FullStatement
EXECUTEsp_executesql@FullStatement
/*
--也可用EXECUTE()執行相同的語句
EXECUTE(@FullStatement)
*/
END
筆者在此設置@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語句集成到實際的代碼中，需謹慎對待。因為這種代碼可以將一些潛在的漏洞引入到你的系統中;如果你認真對待了，這種代碼可以靈活為你地解決一些問題。
