SQL游标优化需求：按邮箱发送关联数据的单份邮件

问题核心 #

临时表##LEAVERUPDATE中同一邮箱对应多行设备数据，原游标会为每一行数据重复发送包含全部关联行的邮件，需优化为每个邮箱仅发送1封包含所有关联设备数据的邮件，同时通过游标完成表内记录的更新操作。

修改后代码 #

DECLARE @SUBJECT VARCHAR(100)
DECLARE @TABLEHTML VARCHAR(MAX) 
DECLARE @TPROFILE VARCHAR(128) = '你的邮件配置文件名' -- 替换为实际数据库邮件配置文件名称
DECLARE @EMAIL VARCHAR(60)
DECLARE @SUPERVISOREMAIL VARCHAR(60)
DECLARE @USERNAME VARCHAR(MAX)

-- 游标仅遍历唯一的邮箱+主管邮箱组合，避免重复处理
DECLARE CSR CURSOR
FOR SELECT DISTINCT EMAIL, SUPERVISOREMAIL, USERNAME 
    FROM ##LEAVERUPDATE
    WHERE UPDATEDINVFIRE = 0 -- 仅处理未标记为已更新的记录（可根据业务调整）

OPEN CSR 
FETCH NEXT FROM CSR INTO @EMAIL, @SUPERVISOREMAIL, @USERNAME

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 生成包含当前邮箱所有关联设备的HTML邮件内容
    SET @SUBJECT = 'VFIRE DEVICE STATUS UPDATED FOR LEAVER'
    SET @TABLEHTML = N'<P> The following user has had the following devices set to leaver status in vFire</P>' +
                N'<P> Please be advised, these devices need to be returned immediately prior to the person leaving the business</P>' +
                N'<table border="1">' +
                N'<tr><th>CI Number</th><th>Username</th><th>Asset Model</th><th>Asset Description</th><th>Users Email Address</th><th>Supervisor EMail Address</th></tr>' +
                CAST
                    ((
                        SELECT 
                        TD = ASSETREF, '',
                        TD = USERNAME, '',
                        TD = DEVICEMODEL, '',
                        TD = DESCRIPTION,'',
                        TD = EMAIL, '',
                        TD = SUPERVISOREMAIL,'' 
                        FROM ##LEAVERUPDATE
                        WHERE EMAIL = @EMAIL
                        FOR XML PATH('TR'), TYPE
                        ) AS NVARCHAR(MAX) ) +
                            N'</TABLE>' +
                                N'<P> </P>'; 

    -- 发送邮件
    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = @TPROFILE,
        @recipients = @SUPERVISOREMAIL,
        @copy_recipients='',
        @blind_copy_recipients ='',
        @subject = @SUBJECT,
        @body = @TABLEHTML,
        @body_format = 'HTML'

    -- 完成表更新：标记当前邮箱的所有记录为已处理
    UPDATE ##LEAVERUPDATE
    SET UPDATEDINVFIRE = 1,
        DATEUPDATEDINVFIRE = GETDATE()
    WHERE EMAIL = @EMAIL

    FETCH NEXT FROM CSR INTO @EMAIL, @SUPERVISOREMAIL, @USERNAME
END

CLOSE CSR
DEALLOCATE CSR

关键改动说明 #

1. 游标查询优化：使用DISTINCT关键字仅获取唯一的邮箱相关组合，确保每个邮箱仅被遍历一次，从根源避免重复发送邮件。
2. 补充表更新逻辑：邮件发送完成后，更新当前邮箱对应的所有记录的状态字段，标记为已处理，满足业务对表更新的要求。
3. 过滤重复处理：游标查询中添加WHERE UPDATEDINVFIRE = 0条件，避免重复处理已发送过邮件的记录，提升执行效率。
4. 完善邮件配置：补充@TPROFILE的赋值提示，需替换为实际的数据库邮件配置文件名，确保邮件发送功能正常运行。

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