搜尋此網誌

2012年5月16日 星期三

如何動態組出 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  

沒有留言:

張貼留言