SQL Server定时任务执行存储过程如何实现邮件仅单次发送

现有SQL Server存储过程由代理作业每7分钟调度执行，逻辑为检测到符合条件的记录时向指定收件人发送通知邮件，当前逻辑在异常状态持续存在时会重复发送邮件，要求不修改原业务Table表的字段，实现条件触发后仅发送一次通知。

以下方案均不侵入原业务表结构，可根据业务场景选择：

方案1：独立通知日志表（推荐，可靠性最高） #

单独创建一张轻量的日志表记录通知发送状态，完全和业务表解耦，同时支持两种常见的通知去重逻辑：按单条业务记录去重（每条异常记录仅提醒一次）、按状态变更去重（异常从无到有时提醒一次，异常持续存在不重复提醒，恢复后再次触发才重发）。

场景A：每条异常记录仅发一次通知 #

1. 先创建独立日志表：

-- 通知发送日志表，与业务表完全独立
CREATE TABLE dbo.NotificationSendLog (
    LogID BIGINT IDENTITY(1,1) PRIMARY KEY,
    -- 字段类型和原业务表的主键/唯一标识字段类型保持一致，int主键就改成INT类型
    BizRecordID UNIQUEIDENTIFIER NOT NULL,
    NotifyType VARCHAR(50) NOT NULL, -- 多通知场景下做区分，当前场景可固定为'BusinessAlert'
    SendTime DATETIME NOT NULL DEFAULT(GETDATE()),
    -- 建联合唯一索引，避免并发场景下重复写入
    CONSTRAINT UQ_NotificationSendLog_BizID_Type UNIQUE(BizRecordID, NotifyType)
)

2. 改造存储过程逻辑，核心是发信前排除已经发过通知的记录，发信后写入日志：

BEGIN
    SET NOCOUNT ON;
    DECLARE @recordCount INT;
    DECLARE @bodytext VARCHAR(200);

    -- 统计【符合条件且未发送过通知】的记录数
    SELECT @recordCount = COUNT(*)
    FROM dbo.Table t
    INNER JOIN dbo.Table2 t2 ON ......... -- 替换为原有的关联条件
    WHERE ............. -- 替换为原有的过滤条件
    AND NOT EXISTS (
        SELECT 1 FROM dbo.NotificationSendLog l
        WHERE l.BizRecordID = t.ID -- t.ID替换为原表的唯一标识字段
        AND l.NotifyType = 'BusinessAlert'
    );

    IF @recordCount > 0
    BEGIN
        SET @bodytext = N'Message';
        EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'Profile',
            @recipients = 'Test@test.com',
            @subject = 'Title',
            @Body = @bodytext,
            -- 查询内容直接排除已发过通知的记录，避免跨会话临时表权限问题
            @query = N'
                SELECT t.Column1,t2.Column2
                FROM DB.dbo.Table t
                INNER JOIN DB.dbo.Table2 t2 ON ......... -- 原关联条件
                WHERE ........................ -- 原过滤条件
                AND NOT EXISTS (
                    SELECT 1 FROM DB.dbo.NotificationSendLog l
                    WHERE l.BizRecordID = t.ID
                    AND l.NotifyType = ''BusinessAlert''
                )', 
            @execute_query_database = N'DB';

        -- 发信完成后，把本次通知的记录写入日志，下次执行就不会重复匹配
        INSERT INTO dbo.NotificationSendLog (BizRecordID, NotifyType)
        SELECT t.ID, 'BusinessAlert'
        FROM dbo.Table t
        INNER JOIN dbo.Table2 t2 ON ......... -- 原关联条件
        WHERE ............. -- 原过滤条件
        AND NOT EXISTS (
            SELECT 1 FROM dbo.NotificationSendLog l
            WHERE l.BizRecordID = t.ID
            AND l.NotifyType = 'BusinessAlert'
        );
    END
END

场景B：异常状态从无到有时发一次，持续异常不重复发 #

如果你的业务需求是「只要有异常就发一次提醒，直到异常全部消除，之后再出现异常才发第二次」，不需要记录单条业务ID，日志表可以更轻量：

1. 建状态记录表：

CREATE TABLE dbo.NotificationStatusLog (
    LogID INT IDENTITY(1,1) PRIMARY KEY,
    NotifyType VARCHAR(50) NOT NULL UNIQUE,
    LastCheckRecordCount INT NOT NULL DEFAULT(0),
    LastSendTime DATETIME NULL
);
-- 初始化当前通知类型的状态
INSERT INTO dbo.NotificationStatusLog(NotifyType) VALUES('BusinessAlert');

2. 改造存储过程判断逻辑：

BEGIN
    SET NOCOUNT ON;
    DECLARE @recordCount INT;
    DECLARE @bodytext VARCHAR(200);
    DECLARE @LastCheckCount INT;

    -- 统计当前符合条件的总记录数
    SELECT @recordCount = ISNULL(COUNT(*),0)
    FROM dbo.Table t
    INNER JOIN dbo.Table2 t2 ON .........
    WHERE .............;

    -- 读取上次检测的记录数
    SELECT @LastCheckCount = LastCheckRecordCount
    FROM dbo.NotificationStatusLog
    WHERE NotifyType = 'BusinessAlert';

    -- 仅当上次无异常、本次有异常（状态从正常变异常）时发信
    IF @recordCount >=1 AND @LastCheckCount = 0
    BEGIN
        SET @bodytext = N'Message';
        EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'Profile',
            @recipients = 'Test@test.com',
            @subject = 'Title',
            @Body = @bodytext,
            @query = N'Select Column1,Column2 from DB.dbo.Table inner join DB.dbo.Table2 on ......... where ........................', 
            @execute_query_database = N'DB';

        -- 更新状态为已发信
        UPDATE dbo.NotificationStatusLog
        SET LastCheckRecordCount = @recordCount, LastSendTime = GETDATE()
        WHERE NotifyType = 'BusinessAlert';
    END
    -- 本次无异常时，重置状态，等待下次触发
    ELSE IF @recordCount = 0
    BEGIN
        UPDATE dbo.NotificationStatusLog
        SET LastCheckRecordCount = 0
        WHERE NotifyType = 'BusinessAlert';
    END
    -- 其余场景（持续有异常、持续无异常）不做操作
END

方案2：依赖系统表判断（无新建表，可靠性一般） #

如果不想额外建表，可以直接读取SQL Server系统表的作业执行记录、邮件发送记录做判断，逻辑为：检测到符合条件的记录时，查询同主题的通知邮件在上次作业成功执行后有没有发送过，没发过才发。
示例判断逻辑：

DECLARE @LastJobRunTime DATETIME;
DECLARE @LastMailTime DATETIME;

-- 替换为你的定时作业ID、执行存储过程的步骤名，查询上次作业成功运行的时间
SELECT TOP 1 @LastJobRunTime = msdb.dbo.agent_datetime(run_date, run_time)
FROM msdb.dbo.sysjobhistory
WHERE job_id = '你的作业ID' AND step_name = '步骤名' AND run_status = 1
ORDER BY instance_id DESC;

-- 查询同主题、同收件人的邮件上次发送时间
SELECT TOP 1 @LastMailTime = send_request_date
FROM msdb.dbo.sysmail_mailitems
WHERE subject = 'Title' AND recipients = 'Test@test.com'
ORDER BY send_request_date DESC;

-- 存在符合条件的记录，且上次发信时间早于上次作业运行时间（也就是上次作业跑的时候没发过），才发信
IF @recordCount > 0 AND (@LastMailTime IS NULL OR @LastMailTime < @LastJobRunTime)
BEGIN
    -- 执行原有的发邮件逻辑
END

• 该方案缺点：如果作业历史被清理、邮件记录被归档、作业执行重叠，可能出现重复发或漏发，仅适合测试环境或可靠性要求不高的场景。

方案3：基于业务时间戳判断（适合仅提醒新增异常的场景） #

如果业务表自带CreateTime/UpdateTime时间字段，不需要关联日志表，只需要单独存一个上次发信的时间点，每次只查询这个时间点之后新增/更新的符合条件的记录，发完更新上次发信时间即可。

• 该方案缺点：如果历史异常数据被修改后重新符合条件，会出现漏发，仅适合明确只提醒新产生异常的场景。

• 注意：所有方案均未修改原业务Table表的结构，满足要求。生产环境优先选方案1，逻辑可控、易排查，日志表数据量增长极慢，只需要每年做一次归档即可。

内容的提问来源于stack exchange，提问作者Freedom7