如何動態組出 T-SQL 指令

sp_executesql 系統預存程序具備以參數來指定所要執行的 T-SQL，藉由這個特性，可以讓動態組出的 T-SQL 更加安全。


假設我們要動態組出 T-SQL，從 AdventureWorks 資料庫中的 Production.Product 資料表內，找出「顏色」是黑色的產品，可以使用下面的程式碼：


USE AdventureWorks
GO
 
-- 宣告變數
DECLARE @SQLCommand nvarchar(200)
DECLARE @columnList nvarchar(50)
DECLARE @parmColor varchar(10)
 
-- 定義變數
SET @columnList = N'ProductID 產品編號, Color 顏色'
SET @parmColor = 'Black'
 
-- 動態組出 T-SQL
SET @SQLCommand = 'SELECT ' + @columnList + ' FROM Production.Product WHERE Color = @color'
 
-- 執行動態組出的 T-SQL
EXECUTE sp_executesql @SQLCommand, N'@color varchar(10)', @color = @parmColor

執行的部分結果畫面：

從程式碼中，可以看到系統預存程序 sp_executesql 一共需要傳入 3 個參數，分別說明如下：


●第 1 個參數：
所要執行的 T-SQL 陳述式，其內容必須是 Unicode 字串，所以在宣告 @SQLCommand 時，必須指定為 n 開頭的資料型別，在這個範例我是使用 nvarchar 型別。
因為要把英文的欄位名稱改用中文來顯示，所以在定義變數 @columnList 時，要使用前置詞 N 來指定該變數的內容。


●第 2 個參數：
它會包含第 1 個參數中，所有內嵌的參數定義。以本程式碼為例，@color 就是內嵌的參數定義。同樣的，這個參數也必須使用前置詞 N 來指定該變數的內容，且每個參數的定義都是由參數名稱（在該程式碼內，就是 @color）和資料型別（在該程式碼中，就是 varchar(10)）所組成。


●第 3 個參數：
指定第 2 個參數內的參數定義的值。以本程式碼為例，第二個參數指定為 @color varchar(10)，所以要把先前設定的 @parmColor 的值指派給 @color，這樣才能讓 @parmColor 所代表的 Black 傳遞給 @color。


大家可以跟上一篇分享：「如何動態組出 T-SQL 指令（上）」，比較一下，於設定 @color 顏色變數的值時，上一篇文章在變數值的左右各用了 3 個單引號（'）來把 Black 包起來，而在這裡只需要用 1 對單引號就可以了。


如果現在要再加上一個查詢的限制條件，就是「產品編號」要小於 325，就要多宣告並設定一個新變數 @parmPID，同時在系統預存程序 sp_executesql 也要隨之修改：



USE AdventureWorks  
GO  
  
-- 宣告變數  
DECLARE @SQLCommand nvarchar(200)  
DECLARE @columnList nvarchar(50)  
DECLARE @parmColor varchar(10)  
DECLARE @parmPID int  
  
-- 定義變數  
SET @columnList = N'ProductID 產品編號, Color 顏色'  
SET @parmColor = 'Black'  
SET @parmPID = 325  
  
-- 動態組出 T-SQL  
SET @SQLCommand = 'SELECT ' + @columnList + ' FROM Production.Product WHERE Color = @color AND ProductID < @pID'  
  
-- 執行動態組出的 T-SQL  
EXECUTE sp_executesql @SQLCommand, N'@color varchar(10), @pID int',  
                        @color = @parmColor, @pID = @parmPID  

執行結果：

如果不想使用 EXECUTE 或 sp_executesql 來動態組出 T-SQL，可以改用下面的程式碼



USE AdventureWorks  
GO  
  
-- 宣告變數  
DECLARE @parmColor varchar(10)  
DECLARE @parmPID int  
  
-- 定義變數  
SET @parmColor = 'Black'  
SET @parmPID = 325  
  
SELECT ProductID 產品編號, Color 顏色  
FROM Production.Product WHERE Color = @parmColor AND ProductID < @parmPID