Transact-SQL 動態指令：使用 Sp_ExecuteSQL (Dynamic SQL Command Using Sp_ExecuteSQL)

通常對 Stored Procedure 的印象，就是將指令寫死 (Hard Coded) 在程式中，較少採取動態的方式來產生。本篇所謂的動態 SQL，就是在開發 Stored Procedure 時，在程式中利用變數組成 SQL 的指令字串；通常可用兩種方法來執行，即 sp_executesql 和 exec。本文將針對 sp_executesql 進行介紹，希望能對各位有所助益。

再提醒一點，SQL 動態指令可發揮強大的功能，它實現了靈活的開發方式；但凡事有利便有弊，它也可能成為資安的漏洞，讓駭客有可趁之機 (如：SQL Injection)。所以當您用這種方式進行開發時，請特別注意使用時機與防護機制。

NOTE 以下範例旨在協助您了解所介紹的內容，於實際開發時，請注意 SQL Injection 的問題。


首先，建立測試資料 (加入 5 筆測試資料於 DETAIL_RECORD Table 中)。

Create Table DETAIL_RECORD(
  Record_Date      datetime,
  Expense_Name     nvarchar(50),
  Expense_Amount   int,
  Memo             nvarchar(500)
Constraint PK_HIS_DETAIL_RECORD Primary Key (Record_Date));

Insert Into DETAIL_RECORD (Record_Date, Expense_Name, Expense_Amount, Memo)
 Values ('2012/01/01 08:00:00', '花費 1', 10000, '說明 1');
Insert Into DETAIL_RECORD (Record_Date, Expense_Name, Expense_Amount, Memo)
 Values ('2012/01/02 09:00:00', '花費 2', 20000, '說明 2');
Insert Into DETAIL_RECORD (Record_Date, Expense_Name, Expense_Amount, Memo)
 Values ('2012/01/02 10:00:00', '花費 3', 30000, '說明 3');
Insert Into DETAIL_RECORD (Record_Date, Expense_Name, Expense_Amount, Memo)
 Values ('2012/01/02 20:00:00', '花費 4', 40000, '說明 4');
Insert Into DETAIL_RECORD (Record_Date, Expense_Name, Expense_Amount, Memo)
 Values ('2012/01/05 21:00:00', '花費 5', 50000, '說明 5');

再建立測試 Stored Procedure (找出該期間的第一筆花費明細)

if object_id ('usp_First_Expense') is not null
 drop proc usp_First_Expense
go

Create Proc usp_First_Expense @prm_start_datetime   datetime,
                              @prm_end_datetime     datetime,
                              @rtn_record_date      datetime     output,
                              @rtn_expense_name     nvarchar(50) output,
                              @rtn_expense_amount   int          output
as
   set nocount on
   
   select top 1 @rtn_record_date    = Record_Date,
                @rtn_expense_name   = Expense_Name,
                @rtn_expense_amount = Expense_Amount  
     from DETAIL_RECORD
    where Record_Date >= @prm_Start_Datetime
      and Record_Date <  @prm_End_Datetime
    order by Record_Date asc

   set nocount off
   return

以下開始解說...

新增資料：利用變數值組成 INSERT 指令字串，再以 sp_executesql 執行。

declare @i_name   nvarchar(50),
        @i_amount int,
        @i_memo   nvarchar(500),
        @sql      nvarchar(2000)

set @i_name   = '花費 6'
set @i_amount = 990
set @i_memo   = '說明 6'

set @sql = 'insert into DETAIL_RECORD (Record_Date, Expense_Name, Expense_Amount, Memo) ' + 
              'values(' + '''' + cast(getdate() as varchar(20)) + '''' + ', '  +
                        + '''' + @i_name                        + '''' + ', '  +
                               + cast(@i_amount as varchar(10))        + ', '  +
                        + '''' + @i_memo                        + '''' + '); '

exec sp_executesql @sql 

-- 執行結果
select * from DETAIL_RECORD

-- 組成的動態查詢字串
select @sql as Your_SQL_String

修改資料：利用變數值組成 UPDATE 指令字串，再以 sp_executesql 執行。
[範例] 將 2012/01/05 20:00:00 ~ 23:59:59 間的花費名稱，改為 "額外花費"。

declare @i_name             nvarchar(50),
        @i_start_datetime   datetime,
        @i_end_datetime     datetime,
        @sql                nvarchar(2000)

set @i_name = '額外花費'
set @i_start_datetime = '2012/01/05 20:00:00'
set @i_end_datetime   = '2012/01/06 00:00:00'

set @sql = 'update DETAIL_RECORD '  + 
           '   set Expense_Name = ' + '''' + @i_name                                + '''' +
           ' where Record_Date >= ' + '''' + cast(@i_start_datetime as varchar(20)) + '''' +
           '   and Record_Date <  ' + '''' + cast(@i_end_datetime as varchar(20))   + '''' 

exec sp_executesql @sql 

-- 執行結果
select * from DETAIL_RECORD

-- 組成的動態查詢字串
select @sql as Your_SQL_String

刪除資料：利用變數值組成 DELETE 指令字串，再以 sp_executesql 執行。
[範例] 刪除 2012/01/06 00:00:00 以後的所有資料

declare @i_datetime         datetime,
        @sql                nvarchar(2000)

set @i_datetime = '2012/01/06 00:00:00'

set @sql = 'delete from DETAIL_RECORD '  + 
           ' where Record_Date > ' + '''' + cast(@i_datetime as varchar(20)) + ''''
           
exec sp_executesql @sql 

-- 執行結果
select * from DETAIL_RECORD

-- 組成的動態查詢字串
select @sql as Your_SQL_String

查詢資料 & 接收回傳值：利用變數值組成 SELECT 指令字串，再以 sp_executesql 執行。當需要針對資料逐筆處理、且藉由變數值重組查詢字串時，可採取此種方式(較常使用在 Stored Procedure 的 Cursor 中)。這裡出現一個問題，該如何接收動態 SQL 指令的結果回傳值呢? 其實並不困難，sp_executesql 可透過參數將值傳出來。在以下範例中，sp_executesql 會將輸出值傳給參數 @inner1、@inner2、@inner3、@inner4；而我們就用 o_record_date、o_expense_name、o_expense_amount、o_memo 4 個參數來做一對一的接收。也就是說，所查詢的結果會被指定給 o_record_date、o_expense_name、o_expense_amount、o_memo 等參數。
[範例] 查詢 2012/1/2 最後一筆花費

declare @boundary_datetime  datetime,
        @i_datetime         datetime,
        @o_record_date      datetime,
        @o_expense_name     nvarchar(50),
        @o_expense_amount   int,        
        @o_memo             nvarchar(500),
        @sql                nvarchar(2000)

set @i_datetime = '2012/01/03'

set @sql = 'select top 1 @inner1 = Record_Date, @inner2 = Expense_Name, @inner3 = Expense_Amount, @inner4 = Memo ' +
           '  from DETAIL_RECORD ' +
           ' where Record_Date < ' + '''' + cast(@i_datetime as varchar(20)) + '''' +
           ' order by Record_Date desc; '
           
exec sp_executesql @sql,
     N'@inner1 datetime output, @inner2 nvarchar(50) output, @inner3 int output, @inner4 nvarchar(500) output ', 
     @inner1 = @o_record_date      output,
     @inner2 = @o_expense_name     output,
     @inner3 = @o_expense_amount   output,
     @inner4 = @o_memo             output

-- 執行結果
select @o_record_date as Record_Date, @o_expense_name as Expense_Name, @o_expense_amount as Expense_Amount, @o_memo as Memo

-- 組成的動態查詢字串
select @sql as Your_SQL_String

定義 Cursor
[範例] 以 Cursor 讀出 Table 資料

declare @sql                  nvarchar(2000),
        @tmp_record_date      datetime,
        @tmp_expense_name     nvarchar(50),
        @tmp_expense_amount   int,
        @tmp_memo             nvarchar(500)

-- 定義 Cursor
set @sql = 'declare TestCSR cursor for ' +
              'select Record_Date, Expense_Name, Expense_Amount, Memo ' +
              '  from DETAIL_RECORD; ' 

exec sp_executesql @sql 

-- 測試 Cursor 是否建置成功
open TestCSR

fetch next from TestCSR
 into @tmp_record_date, @tmp_expense_name, @tmp_expense_amount, @tmp_memo
 
while @@fetch_status = 0
 begin
    select @tmp_record_date as Record_Date, @tmp_expense_name as Expense_Name, @tmp_expense_amount as Expense_Amount, @tmp_memo as Memo
 
    fetch next from TestCSR
     into @tmp_record_date, @tmp_expense_name, @tmp_expense_amount, @tmp_memo
 end
 
close TestCSR
deallocate TestCSR
 
-- 組成的動態查詢字串
select @sql as Your_SQL_String

呼叫 Stored Procedure & 接收回傳值

建議直接使用 exec，請參考 這篇文章 (於該篇文章中，以「呼叫 Stored Procedure & 接收回傳值」為關鍵字進行搜尋)。下例為 sp_executesql 搭配 exec 使用方式：
[範例] 呼叫 Stored Procedure (usp_First_Expense) 找出 2012/01/02 00:00:00 至 2012/01/03 00:00:00 期間的第一筆花費

declare @i_start_datetime   datetime,
        @i_end_datetime     datetime,
        @o_record_date      datetime,
        @o_expense_name     nvarchar(50),
        @o_expense_amount   int,
        @sql                nvarchar(2000)

set @i_start_datetime = '2012/01/02 00:00:00'
set @i_end_datetime   = '2012/01/03 00:00:00'
 
set @sql = 'exec usp_First_Expense '  + '''' + cast(@i_start_datetime as varchar(20)) + '''' + ', ' +
                                      + '''' + cast(@i_end_datetime as varchar(20))   + '''' + ', ' +
                                             + '@inner1 output, ' +
                                             + '@inner2 output, ' +
                                             + '@inner3 output' 

exec sp_executesql @sql,
     N'@inner1 datetime output, @inner2 nvarchar(50) output, @inner3 int output ', 
     @inner1 = @o_record_date      output,
     @inner2 = @o_expense_name     output,
     @inner3 = @o_expense_amount   output   
   
-- 執行結果
select @o_record_date as Record_Date, @o_expense_name as Expense_Name, @o_expense_amount as Expense_Amount

-- 組成的動態查詢字串
select @sql as Your_SQL_String 

欲直接使用 EXEC 指令的讀者，請參考我的另一篇文章：Transact-SQL 動態指令：使用 EXEC (Dynamic SQL Command Using EXEC)。