詳解SQL Server中的動態SQL概念

動態SQL：code that is executed dynamically。它一般是根據使用者輸入或外部條件動態組合的SQL語句塊。動態SQL能靈活的發揮SQL強大的功能、方便的解決一些其它方法難以解決的問題。相信使用過動態SQL的人都能體會到它帶來的便利，然而動態SQL有時候在執行效能(效率)上面不如靜態SQL，而且使用不恰當，往往會在安全方面存在隱患(SQL 注入式攻擊)。

動態SQL可以通過EXECUTE 或SP_EXECUTESQL這兩種方式來執行。來自MSDN）

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 WHERE GroupName=@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 WHERE GroupName=@GroupName' 
  
PRINT @Sql;  
  
EXEC SP_EXECUTESQL @Sql, N'@GroupName NVARCHAR',@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);   
  
SELECT cacheobjtype, objtype, usecounts, sql  
  
FROM sys.syscacheobjects  
  
WHERE sql NOT LIKE '%cache%' 
  
  AND sql NOT LIKE '%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' 
  
EXECUTE SP_EXECUTESQL @Sql, N'@GroupName NVARCHAR(50)', @GroupName;   
  
SELECT cacheobjtype, objtype, usecounts, sql  
  
FROM sys.syscacheobjects  
  
WHERE sql NOT LIKE '%cache%' 
  
  AND sql NOT LIKE '%sys.%'; 
 

執行結果如所示：

 

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

原文標題：SQL Server 動態SQL

連結：http://www.cnblogs.com/kerrycode/archive/2010/08/05/1792671.html

編輯精選】