SQL Server 結合預存程序與 Database Mail 實現定時發送郵件

架構概覽

核心組件：
1. Database Mail – 基於 SMTP 的郵件發送子系統 (需先啟用 Service Broker)。
2. 預存程序 – 封裝業務邏輯、組裝 HTML 郵件內容、呼叫 msdb.dbo.sp_send_dbmail。
3. SQL Server Agent 作業 – 排程觸發預存程序，實現定時自動發送。

前置條件

• 已在 SSMS「管理」→「Database Mail」完成設定檔與帳號設定 (SMTP 伺服器、認證、啟用 Database Mail XPs)。
• SQL Server Agent 服務處於執行中。

預存程序範例：每日發送異常還款清單

CREATE PROCEDURE dbo.sp_send_repayment_alert
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @mail_body NVARCHAR(MAX) = N'';
    DECLARE @subject NVARCHAR(200) = N'每日異常還款監控報表 - ' + CONVERT(VARCHAR(10), GETDATE(), 120);
    DECLARE @profile NVARCHAR(128) = N'DB_Mail_Profile';  -- 對應設定檔名稱

    -- 1. 以游標逐筆組裝 HTML 表格列 (示範用，實務建議改用 FOR XML PATH)
    DECLARE @br_id VARCHAR(50), @installments VARCHAR(10), @remark VARCHAR(200);
    DECLARE cur CURSOR LOCAL FAST_FORWARD FOR
        SELECT br_id, installments, remark
        FROM dw_account_repayment
        WHERE status <> 1 AND is_del = 0
        ORDER BY add_datetime DESC;

    OPEN cur;
    FETCH NEXT FROM cur INTO @br_id, @installments, @remark;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @mail_body += N'' + @br_id + N'' + @installments + N'' + @remark + N'';
        FETCH NEXT FROM cur INTO @br_id, @installments, @remark;
    END
    CLOSE cur; DEALLOCATE cur;

    -- 2. 若有資料則發送
    IF LEN(@mail_body) > 0
    BEGIN
        SET @mail_body = N'
自動還款異常清單
' 
                        + @mail_body + N'
標ID
期數
錯誤描述
';

        EXEC msdb.dbo.sp_send_dbmail
            @profile_name = @profile,
            @recipients = N'alert@example.com;dba@example.com',
            @subject = @subject,
            @body = @mail_body,
            @body_format = 'HTML',
            @importance = 'HIGH';
    END
END
GO

效能優化建議：以 FOR XML PATH 取代游標

SELECT @mail_body = (
    SELECT 
        td = br_id,       '',
        td = installments,'',
        td = remark,      ''
    FROM dw_account_repayment
    WHERE status <> 1 AND is_del = 0
    ORDER BY add_datetime DESC
    FOR XML PATH('tr'), TYPE
).value('.', 'NVARCHAR(MAX)');

單陳述式產生所有 <tr>，避免游標循環開銷，大幅提升大數據量下組裝速度。

建立 Agent 作業排程

1. SSMS → SQL Server Agent → 作業 → 右鍵「新增作業」。
2. 「步驟」頁籤：新增步驟，類型 T-SQL，指令 EXEC dbo.sp_send_repayment_alert;，資料庫選 msdb 或業務庫。
3. 「排程」頁籤：新增每日、每週或自訂排程 (如每天 08:00 執行)。
4. 「通知」頁籤：可設定作業失敗時以 Database Mail 通知 DBA。

常見問題排查

結語

「預存程序 + Database Mail + Agent 作業」是 SQL Server 內建的輕量級自動化通知方案，無需外部排程器即可實現可靠的定時郵件發送。善用 FOR XML PATH 優化內容組裝，配合完善的錯誤日誌監控，能構建穩健的營運通知體系。