透過預存程序動態建立 SQL Server Agent 排程作業：支援參數化、多頻率、自動重試

適用情境

• 應用程式 (ASP.NET 等) 需依使用者輸入動態建立/修改排程作業。
• 避免手動在 SSMS 點擊建立，支援參數化 T-SQL 指令。
• 需支援「每日/每週/每月」、「每 N 分鐘重複」、「指定起始時間」。

核心預存程序：p_createjob

CREATE PROCEDURE [dbo].[p_createjob]
    @jobname      VARCHAR(100),      -- 作業名稱 (唯一)
    @sql          VARCHAR(8000),     -- 要執行的 T-SQL 批次
    @dbname       SYSNAME = '',      -- 目標資料庫，預設目前 DB
    @freqtype     VARCHAR(6) = 'day',-- 頻率類型: 'day'|'week'|'month'
    @fsinterval   INT = 1,           -- 重複間隔 (分鐘)，1 表示不重複
    @time         INT = 170000       -- 起始時間 HHMMSS，如 170000 = 17:00
AS
BEGIN
    SET NOCOUNT ON;
    IF ISNULL(@dbname,'') = '' SET @dbname = DB_NAME();

    -- 1. 若作業已存在，先刪除 (含歷史記錄)
    IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = @jobname)
        EXEC msdb..sp_delete_job @job_name = @jobname;

    -- 2. 建立作業
    EXEC msdb..sp_add_job @job_name = @jobname;

    -- 3. 建立作業步驟
    EXEC msdb..sp_add_jobstep
        @job_name = @jobname,
        @step_name = N'資料處理',
        @subsystem = N'TSQL',
        @database_name = @dbname,
        @command = @sql,
        @retry_attempts = 5,
        @retry_interval = 5;  -- 分鐘

    -- 4. 解析頻率參數
    DECLARE @freq_type INT, @freq_subday_type INT, @freq_recurrence_factor INT;
    SELECT @freq_type = CASE @freqtype WHEN 'day' THEN 4 WHEN 'week' THEN 8 WHEN 'month' THEN 16 END,
           @freq_subday_type = CASE @fsinterval WHEN 1 THEN 0 ELSE 8 END,  -- 0=一次, 8=分鐘
           @freq_recurrence_factor = CASE @freqtype WHEN 'day' THEN 0 ELSE 1 END;

    IF @fsinterval <> 1 SET @time = 0;  -- 重複執行時從 00:00 起算

    -- 5. 建立排程
    EXEC msdb..sp_add_jobschedule
        @job_name = @jobname,
        @name = N'時間排程',
        @freq_type = @freq_type,
        @freq_interval = 1,
        @freq_subday_type = @freq_subday_type,
        @freq_subday_interval = @fsinterval,
        @freq_recurrence_factor = @freq_recurrence_factor,
        @active_start_time = @time;

    -- 6. 指定目標伺服器 (本機)
    EXEC msdb..sp_add_jobserver
        @job_name = @jobname,
        @server_name = N'(local)';
END

呼叫範例

-- 每天 17:00 執行一次
EXEC p_createjob
    @jobname = N'Daily_Sales_Aggregate',
    @sql = N'EXEC usp_GenerateDailySalesReport;',
    @freqtype = 'day', @fsinterval = 1, @time = 170000;

-- 每週一 03:00 執行
EXEC p_createjob
    @jobname = N'Weekly_Index_Rebuild',
    @sql = N'EXEC usp_RebuildIndexes;',
    @freqtype = 'week', @fsinterval = 1, @time = 30000;
    -- 注意：week 的 @freq_interval 需用位元遮罩 (1=Sun, 2=Mon, 4=Tue...)，此處簡化為 1

-- 每 30 分鐘重複執行 (00:00-23:59)
EXEC p_createjob
    @jobname = N'Realtime_Log_Cleanup',
    @sql = N'DELETE FROM LogTable WHERE LogTime < DATEADD(HOUR,-24,GETDATE());',
    @freqtype = 'day', @fsinterval = 30, @time = 0;

進階擴充建議

• 改為 UPSERT：判斷作業不存在才 sp_add_job，存在則 sp_update_job/sp_update_jobstep/sp_update_jobschedule，保留歷史記錄。
• 支援月份頻率的具體日期：@freq_interval 為日期位元遮罩 (1=1號, 2=2號... 2^27=28號)，@freq_relative_interval 與 @freq_recurrence_factor 配合可做「每月倒數第 2 個週五」等複雜規則。
• 加入通知：sp_add_jobserver 後可用 sp_add_notification 設定失敗寄信給 DBA。
• 參數化 @sql：傳遞預存程序名稱 + 參數值，在步驟中組裝 EXEC proc @p1=v1, @p2=v2，避免動態 SQL 注入風險。

權限需求

執行者需為 sysadmin 固定伺服器角色成員，或具備 msdb 的 SQLAgentUserRole / SQLAgentOperatorRole 角色權限。