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 2005 擴充了 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(50);SET@GroupName ='SuperAdmin';

　　EXECUTE('SELECT * FROM Groups WHERE GroupName=''' + SUBSTRING(@GroupName, 1,5) + ''''); --'SUBSTRING' 附近有語法錯誤.

　　DECLARE @Sql VARCHAR(200);

　　DECLARE @GroupName VARCHAR(50);SET@GroupName ='SuperAdmin';

　　SET@Sql='SELECT * FROM Groups WHERE GroupName=''' + SUBSTRING(@GroupName, 1,5) + ''''

　　--PRINT @Sql;EXECUTE(@Sql);

　　Summary:EXECUTE 括弧裡面只能是字串變數、字串常量、或它們的串連組合,不能調用其它一些函數、預存程序等. 如果要使用,則使用變數組合,如上所示.

　　DECLARE @Sql VARCHAR(200);

　　DECLARE @GroupName VARCHAR(50);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(200);

　　DECLARE @GroupName NVARCHAR(50);SET@GroupName ='SuperAdmin';

　　SET@Sql='SELECT * FROM Groups WHEREGroupName=@GroupName'

　　PRINT @Sql;

　　EXEC SP_EXECUTESQL @Sql,N'@GroupNameNVARCHAR',@GroupName

　　查詢出來沒有結果,沒有聲明參數長度.

　　DECLARE @Sql NVARCHAR(200);

　　DECLARE @GroupName NVARCHAR(50);SET@GroupName ='SuperAdmin';

　　SET@Sql ='SELECT * FROM Groups WHERE GroupName=@GroupName'

　　PRINT @Sql;

　　EXEC SP_EXECUTESQL @Sql, N'@GroupName NVARCHAR(50)',@GroupName

　　Summary:動態批處理不能訪問定義在批處理裡的局部變數 . SP_EXECUTESQL 可以有輸入輸出參數,比EXECUTE靈活.

　　下面我們來看看EXECUTE , SP_EXECUTESQL的執行效率,首先把緩衝清除執行計畫,然後改變用@GroupName值SuperAdmin、CommonUser、CommonAdmin分別執行三次.然後看看其使用緩衝的資訊

　　DBCC FREEPROCCACHE;

　　DECLARE @Sql VARCHAR(200);

　　DECLARE @GroupName VARCHAR(50);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(200);

　　DECLARE @GroupName NVARCHAR(50);SET@GroupName ='SuperAdmin'; --'CommonUser', 'CommonAdmin'

　　SET@Sql ='SELECT * FROM Groups WHERE GroupName=@GroupName'

　　EXECUTESP_EXECUTESQL @Sql, N'@GroupName NVARCHAR(50)', @GroupName;

　　SELECTcacheobjtype, objtype, usecounts, sql

　　FROM sys.syscacheobjects

　　WHERE sql NOTLIKE '%cache%'

　　ANDsql NOTLIKE '%sys.%';

　　Summary:EXEC 產生了三個獨立的 ad hoc 執行計畫,而用SP_EXECUTESQL只產生了一次執行計畫,重複使用了三次,試想如果一個庫裡面,有許多這樣類似的動態SQL,而且頻繁執行,如果採用SP_EXECUTESQL就能提高效能.