動态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);
set @sql = 'select * from groups where groupname=''' + substring(@groupname, 1,5) + ''''
--print @sql;
execute (@sql);
summary：execute 括号裡面隻能是字元串變量、字元串常量、或它們的連接配接組合，不能調用其它一些函數、存儲過程等。 如果要使用，則使用變量組合，如上所示。
set @sql = 'select * from groups where groupname=@groupname'
execute (@sql); --出錯：必須聲明标量變量 "@groupname"。
set @sql = 'select * from groups where groupname=' + quotename(@groupname, '''')
execute (@sql); --正确：
declare @sql nvarchar(200);
declare @groupname nvarchar(50);
print @sql;
exec sp_executesql @sql, n'@groupname nvarchar',@groupname
查詢出來沒有結果，沒有聲明參數長度。
exec sp_executesql @sql, n'@groupname nvarchar(50)',@groupname
summary：動态批處理不能通路定義在批處理裡的局部變量 。 sp_executesql 可以有輸入輸出參數，比execute靈活。
下面我們來看看execute , sp_executesql的執行效率，首先把緩存清除執行計劃，然後改變用@groupname值superadmin、commonuser、commonadmin分别執行三次。然後看看其使用緩存的資訊
dbcc freeproccache;
set @groupname = 'superadmin'; --'commonuser', 'commonadmin'
execute (@sql);
select cacheobjtype, objtype, usecounts, sql
from sys.syscacheobjects
where sql not like '%cache%'
and sql not like '%sys.%';
如下圖所示
依葫蘆畫瓢，接着我們看看sp_executesql的執行效率.
execute sp_executesql @sql, n'@groupname nvarchar(50)', @groupname;
執行結果如下圖所示：
summary：exec 生成了三個獨立的 ad hoc 執行計劃，而用sp_executesql隻生成了一次執行計劃，重複使用了三次，試想如果一個庫裡面，有許多這樣類似的動态sql，而且頻繁執行，如果采用sp_executesql就能提高性能。