SQL Server 2008 WAITFOR 用法完整介紹：延遲執行與定時執行

語法

WAITFOR
{
    DELAY 'time_to_pass'
  | TIME 'time_to_execute'
  | [ ( receive_statement ) | ( get_conversation_group_statement ) ]
    [ , TIMEOUT timeout ]
};

範例一：定時執行 (TIME)

USE msdb;
EXECUTE sp_add_job @job_name = 'TestJob';
BEGIN
    WAITFOR TIME '22:20';  -- 晚上 10:20 執行
    EXECUTE sp_update_job @job_name = 'TestJob', @new_name = 'UpdatedJob';
END; GO

範例二：延遲執行 (DELAY) - 最長 24 小時

BEGIN
    WAITFOR DELAY '02:00';  -- 兩小時後執行
    EXECUTE sp_helpdb;
END; GO

範例三：搭配變數的動態延遲預存程序

USE AdventureWorks2008R2; GO
IF OBJECT_ID('dbo.TimeDelay_hh_mm_ss','P') IS NOT NULL DROP PROCEDURE dbo.TimeDelay_hh_mm_ss; GO
CREATE PROCEDURE dbo.TimeDelay_hh_mm_ss (@DelayLength char(8) = '00:00:00')
AS
BEGIN
    DECLARE @ReturnInfo varchar(255);
    IF ISDATE('2000-01-01 ' + @DelayLength + '.000') = 0
    BEGIN
        SELECT @ReturnInfo = 'Invalid time ' + @DelayLength + ', hh:mm:ss submitted.';
        PRINT @ReturnInfo; RETURN 1;
    END
    WAITFOR DELAY @DelayLength;
    SELECT @ReturnInfo = 'A total time of ' + @DelayLength + ', hh:mm:ss, has elapsed! Your time is up.';
    PRINT @ReturnInfo;
END; GO

EXEC TimeDelay_hh_mm_ss '00:00:10'; GO
-- 結果：A total time of 00:00:10, in hh:mm:ss, has elapsed. Your time is up.

適用場景

• 無權建立 SQL Agent Job 時的輕量級排程替代方案。
• 測試、示範、批次處理中需暫停等待。
• Service Broker 對話等待 (RECEIVE/GET CONVERSATION GROUP)。

注意：WAITFOR 會阻塞工作階段，長時間使用會佔用連線資源，生產環境宜改用 Agent Job 或 Service Broker。