比EXECUTE更強大的SP_EXECUTESQL

以往在資料庫撰寫T-SQL時,如果遇到需要動態產生SQL 語句的時候,都習慣使用字串去湊出完整的SQL語句,並且透過EXECUTE來執行動態拼湊出來的SQL如下:

DECLARE @qryString NVARCHAR(1000)
DECLARE @單位 NVARCHAR(4)
SET @單位='行政管理'
SET @qryString ='SELECT COUNT(*) FROM 員工 WHERE 單位=' + @單位
EXECUTE @qryString

上面可以傳回行政管理單位的總人數.

但是當您除了動態湊SQL語句外，還需要有回傳值的時候，您可能就需要使用更強大,效能更好的SP_EXECUTESQL來實作出您的需求.如下:

DECLARE @qryString NVARCHAR(1000)
DECLARE @rtnCount INT

SET @qryString='SELECT @count=COUNT(*) FROM 員工 WHERE 單位=@單位'
EXEC SP_EXECUTESQL @qryString,N'@count int OUTPUT,@單位 CHAR(4)',@rtnCount OUTPUT,@單位='行政管理'
PRINT @rtnCount

SP_EXECUTESQL的第一個參數存放SQL的語法字串,第二個參數用來宣告語法字串中的所有變數名稱和型態,
如果有多個參數,就用逗號分隔即可,第三個參數用來指定參數的值.另外要注意的是第二個參數需要是N開頭的型態字串(EX:NVARCHAR),所以要記得加上N，否則會出現過程需要參數'@statement' 為'ntext/nchar/nvarchar' 類型"的錯誤訊息。