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

動態 SQL 通常可用 sp_executesql 和 exec 兩種方法來實現，本文將針對 exec 部分進行說明。

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 指令字串，再以 exec 執行。

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 (@sql)

-- 執行結果
select * from DETAIL_RECORD

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

修改資料：利用變數值組成 UPDATE 指令字串，再以 exec 執行。
[範例] 將 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 (@sql)

-- 執行結果
select * from DETAIL_RECORD

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

刪除資料：利用變數值組成 DELETE 指令字串，再以 exec 執行。
[範例] 刪除 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 (@sql)

-- 執行結果
select * from DETAIL_RECORD

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

查詢資料 & 接收回傳值：建議搭配 sp_executesql 使用，請參考 這篇文章 (於該篇文章中，以「查詢資料 & 接收回傳值」為關鍵字進行搜尋)。


定義 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 (@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 & 接收回傳值
[範例] 呼叫 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'
   
exec usp_First_Expense @i_start_datetime, @i_end_datetime, @o_record_date output, @o_expense_name output, @o_expense_amount output
     
-- 執行結果 
select @o_record_date as Record_Date, @o_expense_name as Expense_Name, @o_expense_amount as Expense_Amount 

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