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
執行 TransactSQL 批中的命令字符串字符串或執行下列模塊之一:系統存儲過程用戶定義存儲過程標量值用戶定義函數或擴展存儲過程SQL Server 擴展了 EXECUTE 語句以使其可用於向鏈接服務器發送傳遞命令此外還可以顯式設置執行字符串或命令的上下文
SP_EXECUTESQL
執行可以多次重復使用或動態生成的 TransactSQL 語句或批處理TransactSQL 語句或批處理可以包含嵌入參數在批處理名稱作用域和數據庫上下文方面SP_EXECUTESQL 與 EXECUTE 的行為相同SP_EXECUTESQL stmt 參數中的 TransactSQL 語句或批處理在執行 SP_EXECUTESQL 語句時才編譯隨後將編譯 stmt 中的內容並將其作為執行計劃運行該執行計劃獨立於名為 SP_EXECUTESQL 的批處理的執行計劃SP_EXECUTESQL 批處理不能引用調用 SP_EXECUTESQL 的批處理中聲明的變量SP_EXECUTESQL 批處理中的本地游標或變量對調用 SP_EXECUTESQL 的批處理是不可見的對數據庫上下文所作的更改只在 SP_EXECUTESQL 語句結束前有效
如果只更改了語句中的參數值則 sp_executesql 可用來代替存儲過程多次執行 TransactSQL 語句因為 TransactSQL 語句本身保持不變僅參數值發生變化所以 SQL Server 查詢優化器可能重復使用首次執行時所生成的執行計劃
一般來說我們推薦優先使用SP_EXECUTESQL來執行動態SQL一方面它更加靈活可以有輸入輸出參數另外一方面查詢優化器更有可能重復使用執行計劃提高執行效率還有就是使用SP_EXECUTESQL能提高安全性;當然也不是說要完全擯棄EXECUTE在特定場合下EXECUTE比SP_EXECUTESQL更方便些比如動態SQL字符串是VARCHAR類型不是NVARCHAR類型SP_EXECUTESQL 只能執行是Unicode的字符串或是可以隱式轉換為ntext的常量或變量而EXECUTE則兩種類型的字符串都能執行
下面我們來對比看看EXECUTE 和SP_EXECUTESQL的一些細節地方
EXECUTE(NSELECT * FROM Groups) 執行成功
EXECUTE(SELECT * FROM Groups) 執行成功
SP_EXECUTESQL NSELECT * FROM Groups; 執行成功
SP_EXECUTESQL SELECT * FROM Groups 執行出錯
Summary:EXECUTE 可以執行非Unicode或Unicode類型的字符串常量變量而SP_EXECUTESQL只能執行Unicode或可以隱式轉換為ntext的字符串常量變量
DECLARE @GroupName VARCHAR();SET@GroupName =SuperAdmin;
EXECUTE(SELECT * FROM Groups WHERE GroupName= + SUBSTRING(@GroupName ) + ); SUBSTRING 附近有語法錯誤
DECLARE @Sql VARCHAR();
DECLARE @GroupName VARCHAR();SET@GroupName =SuperAdmin;
SET@Sql=SELECT * FROM Groups WHERE GroupName= + SUBSTRING(@GroupName ) +
PRINT @Sql;EXECUTE(@Sql);
Summary:EXECUTE 括號裡面只能是字符串變量字符串常量或它們的連接組合不能調用其它一些函數存儲過程等 如果要使用則使用變量組合如上所示
DECLARE @Sql VARCHAR();
DECLARE @GroupName VARCHAR();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();
DECLARE @GroupName NVARCHAR();SET@GroupName =SuperAdmin;
SET@Sql=SELECT * FROM Groups WHEREGroupName=@GroupName
PRINT @Sql;
EXEC SP_EXECUTESQL @SqlN@GroupNameNVARCHAR@GroupName
查詢出來沒有結果沒有聲明參數長度
DECLARE @Sql NVARCHAR();
DECLARE @GroupName NVARCHAR();SET@GroupName =SuperAdmin;
SET@Sql =SELECT * FROM Groups WHERE GroupName=@GroupName
PRINT @Sql;
EXEC SP_EXECUTESQL @Sql N@GroupName NVARCHAR()@GroupName
Summary:動態批處理不能訪問定義在批處理裡的局部變量 SP_EXECUTESQL 可以有輸入輸出參數比EXECUTE靈活
下面我們來看看EXECUTE SP_EXECUTESQL的執行效率首先把緩存清除執行計劃然後改變用@GroupName值SuperAdminCommonUserCommonAdmin分別執行三次然後看看其使用緩存的信息
DBCC FREEPROCCACHE;
DECLARE @Sql VARCHAR();
DECLARE @GroupName VARCHAR();SET@GroupName =SuperAdmin; CommonUser CommonAdmin
SET@Sql =SELECT * FROM Groups WHERE GroupName= + QUOTENAME(@GroupName )
EXECUTE(@Sql); SELECTcacheobjtype objtype usecounts sql
FROM syssyscacheobjects
WHERE sql NOTLIKE %cache%
ANDsql NOTLIKE %sys%;
依葫蘆畫瓢接著我們看看SP_EXECUTESQL的執行效率
DBCC FREEPROCCACHE;
DECLARE @Sql NVARCHAR();
DECLARE @GroupName NVARCHAR();SET@GroupName =SuperAdmin; CommonUser CommonAdmin
SET@Sql =SELECT * FROM Groups WHERE GroupName=@GroupName
EXECUTESP_EXECUTESQL @Sql N@GroupName NVARCHAR() @GroupName;
SELECTcacheobjtype objtype usecounts sql
FROM syssyscacheobjects
WHERE sql NOTLIKE %cache%
ANDsql NOTLIKE %sys%;
Summary:EXEC 生成了三個獨立的 ad hoc 執行計劃而用SP_EXECUTESQL只生成了一次執行計劃重復使用了三次試想如果一個庫裡面有許多這樣類似的動態SQL而且頻繁執行如果采用SP_EXECUTESQL就能提高性能
From:http://tw.wingwit.com/Article/program/SQLServer/201311/22279.html
