動(dòng)態(tài)SQL：code that is executed dynamically。它一般是根據(jù)用戶(hù)輸入或外部條件動(dòng)態(tài)組合的SQL語(yǔ)句塊。動(dòng)態(tài)SQL能靈活的發(fā)揮SQL強(qiáng)大的功能、方便的解決一些其它方法難以解決的問(wèn)題。相信使用過(guò)動(dòng)態(tài)SQL的人都能體會(huì)到它帶來(lái)的便利，然而動(dòng)態(tài)SQL有時(shí)候在執(zhí)行性能(效率)上面不如靜態(tài)SQL，而且使用不恰當(dāng)，往往會(huì)在安全方面存在隱患(SQL 注入式攻擊)。

動(dòng)態(tài)SQL可以通過(guò)EXECUTE 或SP_EXECUTESQL這兩種方式來(lái)執(zhí)行。（來(lái)自MSDN）

EXECUTE 執(zhí)行 Transact-SQL 批中的命令字符串、字符串或執(zhí)行下列模塊之一：系統(tǒng)存儲(chǔ)過(guò)程、用戶(hù)定義存儲(chǔ)過(guò)程、標(biāo)量值用戶(hù)定義函數(shù)或擴(kuò)展存儲(chǔ)過(guò)程。SQL Server 2005 擴(kuò)展了 EXECUTE 語(yǔ)句，以使其可用于向鏈接服務(wù)器發(fā)送傳遞命令。此外，還可以顯式設(shè)置執(zhí)行字符串或命令的上下文

SP_EXECUTESQL

執(zhí)行可以多次重復(fù)使用或動(dòng)態(tài)生成的 Transact-SQL 語(yǔ)句或批處理。Transact-SQL 語(yǔ)句或批處理可以包含嵌入?yún)?shù)。在批處理、名稱(chēng)作用域和數(shù)據(jù)庫(kù)上下文方面，SP_EXECUTESQL 與 EXECUTE 的行為相同。SP_EXECUTESQL stmt 參數(shù)中的 Transact-SQL 語(yǔ)句或批處理在執(zhí)行 SP_EXECUTESQL 語(yǔ)句時(shí)才編譯。隨后，將編譯 stmt 中的內(nèi)容，并將其作為執(zhí)行計(jì)劃運(yùn)行。該執(zhí)行計(jì)劃獨(dú)立于名為 SP_EXECUTESQL 的批處理的執(zhí)行計(jì)劃。SP_EXECUTESQL 批處理不能引用調(diào)用 SP_EXECUTESQL 的批處理中聲明的變量。SP_EXECUTESQL 批處理中的本地游標(biāo)或變量對(duì)調(diào)用 SP_EXECUTESQL 的批處理是不可見(jiàn)的。對(duì)數(shù)據(jù)庫(kù)上下文所作的更改只在 SP_EXECUTESQL 語(yǔ)句結(jié)束前有效。

如果只更改了語(yǔ)句中的參數(shù)值，則 sp_executesql 可用來(lái)代替存儲(chǔ)過(guò)程多次執(zhí)行 Transact-SQL 語(yǔ)句。因?yàn)?Transact-SQL 語(yǔ)句本身保持不變，僅參數(shù)值發(fā)生變化，所以 SQL Server 查詢(xún)優(yōu)化器可能重復(fù)使用首次執(zhí)行時(shí)所生成的執(zhí)行計(jì)劃。

一般來(lái)說(shuō)，我們推薦、優(yōu)先使用SP_EXECUTESQL來(lái)執(zhí)行動(dòng)態(tài)SQL，一方面它更加靈活、可以有輸入輸出參數(shù)、另外一方面，查詢(xún)優(yōu)化器更有可能重復(fù)使用執(zhí)行計(jì)劃，提高執(zhí)行效率。還有就是使用SP_EXECUTESQL能提高安全性；當(dāng)然也不是說(shuō)要完全擯棄EXECUTE，在特定場(chǎng)合下，EXECUTE比SP_EXECUTESQL更方便些，比如動(dòng)態(tài)SQL字符串是VARCHAR類(lèi)型、不是NVARCHAR類(lèi)型。SP_EXECUTESQL 只能執(zhí)行是Unicode的字符串或是可以隱式轉(zhuǎn)換為ntext的常量或變量、而EXECUTE則兩種類(lèi)型的字符串都能執(zhí)行。

下面我們來(lái)對(duì)比看看EXECUTE 和SP_EXECUTESQL的一些細(xì)節(jié)地方。

EXECUTE (N'SELECT * FROM Groups')      --執(zhí)行成功  
EXECUTE ('SELECT * FROM Groups')       --執(zhí)行成功  
 
SP_EXECUTESQL N'SELECT * FROM Groups'; --執(zhí)行成功  
SP_EXECUTESQL 'SELECT * FROM Groups'   --執(zhí)行出錯(cuò) 

Summary:EXECUTE 可以執(zhí)行非Unicode或Unicode類(lèi)型的字符串常量、變量。而SP_EXECUTESQL只能執(zhí)行Unicode或可以隱式轉(zhuǎn)換為ntext的字符串常量、變量。

DECLARE @GroupName VARCHAR(50);  
SET @GroupName = 'SuperAdmin';  
EXECUTE ('SELECT * FROM Groups WHERE 
GroupName=''' + SUBSTRING(@GroupName, 1,5) + ''''); --'SUBSTRING' 附近有語(yǔ)法錯(cuò)誤。  
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 括號(hào)里面只能是字符串變量、字符串常量、或它們的連接組合，不能調(diào)用其它一些函數(shù)、存儲(chǔ)過(guò)程等。 如果要使用，則使用變量組合，如上所示。

DECLARE @Sql VARCHAR(200);  
DECLARE @GroupName VARCHAR(50);  
SET @GroupName = 'SuperAdmin';  
SET @Sql = 'SELECT * FROM Groups WHERE GroupName=@GroupName' 
--PRINT @Sql;  
EXECUTE (@Sql);  --出錯(cuò)：必須聲明標(biāo)量變量 "@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  

查詢(xún)出來(lái)沒(méi)有結(jié)果，沒(méi)有聲明參數(shù)長(zhǎng)度。

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：動(dòng)態(tài)批處理不能訪(fǎng)問(wèn)定義在批處理里的局部變量 。 SP_EXECUTESQL 可以有輸入輸出參數(shù)，比EXECUTE靈活。

下面我們來(lái)看看EXECUTE , SP_EXECUTESQL的執(zhí)行效率，首先把緩存清除執(zhí)行計(jì)劃，然后改變用@GroupName值SuperAdmin、CommonUser、CommonAdmin分別執(zhí)行三次。然后看看其使用緩存的信息

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.%'; 

如下圖所示

依葫蘆畫(huà)瓢，接著我們看看SP_EXECUTESQL的執(zhí)行效率.

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.%'; 

執(zhí)行結(jié)果如下圖所示：

Summary：EXEC 生成了三個(gè)獨(dú)立的 ad hoc 執(zhí)行計(jì)劃，而用SP_EXECUTESQL只生成了一次執(zhí)行計(jì)劃，重復(fù)使用了三次，試想如果一個(gè)庫(kù)里面，有許多這樣類(lèi)似的動(dòng)態(tài)SQL，而且頻繁執(zhí)行，如果采用SP_EXECUTESQL就能提高性能。 

原文標(biāo)題：SQL Server 動(dòng)態(tài)SQL

鏈接：http://www.cnblogs.com/kerrycode/archive/2010/08/05/1792671.html

1. SQL Server數(shù)據(jù)庫(kù)和Oracle行轉(zhuǎn)列的特殊方案描述
2. SQL Server行轉(zhuǎn)列的什么情況下被用？
3. SQL Server實(shí)例中對(duì)另個(gè)實(shí)例的調(diào)用
4. SQL Server identity列，美中不足之處
5. SQL Server DateTime數(shù)據(jù)類(lèi)型的另類(lèi)解讀