SQL Server按表行发邮件时内容重复首行值的解决问询

一、问题根源 #

你的代码中，@Body变量在循环外部仅初始化一次。第一次循环执行REPLACE后，模板中的@Database、@RestoreResult、@DbccResult等占位符已经被替换为首行的实际值，后续循环中再调用REPLACE时，找不到对应的占位符，导致邮件内容始终停留在首行数据。

二、快速修复现有游标代码 #

修改思路：每次循环都重新基于原始模板生成当前行的邮件内容，避免占位符被提前替换耗尽。

修改后的代码 #

DECLARE @BCC  VARCHAR(MAX) = ''
DECLARE @TO  VARCHAR(300) = 'a@gmail.com'   
DECLARE @CC  VARCHAR(300) = ''    
DECLARE @Sender  VARCHAR ( 300 ) = (SELECT ParameterValue FROM EmailParameters (NOLOCK) WHERE ParameterLabel = 'Sender')
DECLARE @Subject  VARCHAR(150) = (Select [Subject] FROM [EmailTemplates]  WHERE EmailTemplateID = 10)
-- 保存原始模板，仅处理全局变量（@IP）一次
DECLARE @OriginalBody  NVARCHAR(MAX) = (Select EmailTemplate FROM [EmailTemplates]  WHERE EmailTemplateID = 10) 
DECLARE @IP  VArchar(15) = (SELECT TOP(1) c.local_net_address FROM sys.dm_exec_connections AS c WHERE c.local_net_address IS NOT NULL)
DECLARE @Date  Datetime = GETDATE() 
DECLARE @Database  sysname          
DECLARE @RestoreResult  NVARCHAR (50) 
DECLARE @DbccResult  NVARCHAR (50) 
DECLARE @ID  INT

-- 处理全局变量的替换（仅执行一次）
Set @OriginalBody = REPLACE(@OriginalBody, '@IP', @IP)
Set @Subject = REPLACE(REPLACE(@Subject, '@Sender', @Sender), '@Date', @Date)        

DECLARE BackupCursor CURSOR FOR
SELECT [Database], RestoreResult, DbccResult, ID
FROM BackupTestResults_Alerting
WHERE SentFlag = 0;

OPEN BackupCursor;
FETCH NEXT FROM BackupCursor INTO @Database, @RestoreResult, @DbccResult, @ID;

WHILE (@@fetch_status = 0)
BEGIN
    -- 每次循环重新获取原始模板副本，避免占位符被提前替换
    DECLARE @CurrentBody NVARCHAR(MAX) = @OriginalBody
    -- 替换当前行的变量
    Set @CurrentBody = REPLACE(@CurrentBody, '@Database', @Database)
    Set @CurrentBody = REPLACE(@CurrentBody, '@RestoreResult', @RestoreResult)
    Set @CurrentBody = REPLACE(@CurrentBody, '@DbccResult', @DbccResult) 

    INSERT INTO [EmailQueue] 
    SELECT 10,
           @CurrentBody,
           @To,
           @Cc,
           @Bcc,
           @Subject,
           0   
    EXEC SDP.dbo.usp_Email_Send

    PRINT @dbccResult
    -- 更新已发送标记（避免重复发送）
    UPDATE BackupTestResults_Alerting SET SentFlag = 1 WHERE ID = @ID

    FETCH NEXT FROM BackupCursor INTO @Database, @RestoreResult, @DbccResult, @ID;
END

CLOSE BackupCursor;
DEALLOCATE BackupCursor;  

三、更优雅的无游标批量处理方案 #

游标在处理大量数据时效率较低，推荐使用批量SQL语句替代，更适合整合到备份测试任务中：

DECLARE @BCC VARCHAR(MAX) = ''
DECLARE @TO VARCHAR(300) = 'a@gmail.com'   
DECLARE @CC VARCHAR(300) = ''    
DECLARE @Sender VARCHAR(300) = (SELECT ParameterValue FROM EmailParameters (NOLOCK) WHERE ParameterLabel = 'Sender')
DECLARE @SubjectTemplate VARCHAR(150) = (Select [Subject] FROM [EmailTemplates] WHERE EmailTemplateID = 10)
DECLARE @BodyTemplate NVARCHAR(MAX) = (Select EmailTemplate FROM [EmailTemplates] WHERE EmailTemplateID = 10) 
DECLARE @IP VARCHAR(15) = (SELECT TOP(1) c.local_net_address FROM sys.dm_exec_connections AS c WHERE c.local_net_address IS NOT NULL)
DECLARE @Date Datetime = GETDATE()

-- 预先生成处理好全局变量的模板
DECLARE @FinalSubject VARCHAR(150) = REPLACE(REPLACE(@SubjectTemplate, '@Sender', @Sender), '@Date', @Date)
DECLARE @FinalBodyTemplate NVARCHAR(MAX) = REPLACE(@BodyTemplate, '@IP', @IP)

-- 批量生成所有待发送邮件的内容并插入队列
INSERT INTO [EmailQueue] (TemplateID, Body, [To], Cc, Bcc, [Subject], Status)
SELECT 
    10,
    -- 逐行替换模板变量
    REPLACE(
        REPLACE(
            REPLACE(@FinalBodyTemplate, '@Database', btr.[Database]),
            '@RestoreResult', btr.RestoreResult
        ),
        '@DbccResult', btr.DbccResult
    ),
    @TO,
    @CC,
    @BCC,
    @FinalSubject,
    0
FROM BackupTestResults_Alerting btr
WHERE btr.SentFlag = 0

-- 批量更新已发送标记，防止重复触发告警
UPDATE BackupTestResults_Alerting
SET SentFlag = 1
WHERE SentFlag = 0

-- 统一执行邮件发送（如果存储过程支持批量处理，或由队列监控作业自动处理）
EXEC SDP.dbo.usp_Email_Send

方案优势 #

• 避免游标带来的性能开销，处理大量数据时效率更高
• 代码更简洁，易于维护和整合到备份测试任务中
• 批量操作减少数据库资源占用，适合自动化流程

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