如何改写SQL Server 2016邮件发送逻辑消除重复SQL语句

优先选择表变量/临时表方案，是最适配你场景的解决方式：

• 公共查询仅需编写、执行1次，后续调整逻辑只需要修改一处，完全消除重复代码
• 避免相同查询执行两次，数据量大时性能优势明显
• 实现逻辑简单，不需要额外处理动态SQL的转义、注入风险
  如果公共查询返回的数据量较大（超过1000行），可以把表变量换成临时表#mailData，查询性能更优。

具体实现代码 #

DECLARE @mail varchar(255)=''
DECLARE @mailBody nvarchar(max)=''
-- 定义表变量存储公共查询结果，仅查询1次
DECLARE @mailData TABLE (
    mailBody nvarchar(max),
    mailFooter varchar(50),
    mail varchar(255)
)
-- 公共查询仅编写一次，写入表变量
INSERT INTO @mailData(mailBody, mailFooter, mail)
select 'mail body content-1' as 'mailBody','11/9/221' as 'mailFooter','a@a.com' as 'mail'
union all
select 'mail body content-2' as 'mailBody','11/09/2021' as 'mailFooter','b@b.com' as 'mail'
union all
select 'mail body content-3' as 'mailBody','10/09/2021' as 'mailFooter','a@a.com' as 'mail'

-- 提前按收件人聚合所有邮件内容，减少循环内查询逻辑
DECLARE @mailAgg TABLE (
    mail varchar(255),
    mailBody nvarchar(max)
)
INSERT INTO @mailAgg(mail, mailBody)
SELECT 
    m.mail,
    -- 增加TYPE和.value处理，避免XML转义特殊字符（<>&等）
    (
        SELECT c.mailBody as 'span', c.mailFooter as 'small' 
        FROM @mailData c 
        WHERE c.mail = m.mail 
        FOR XML PATH ('div'), TYPE
    ).value('.', 'nvarchar(max)') as mailBody
FROM @mailData m
GROUP BY m.mail

-- 游标直接遍历聚合后的结果即可
DECLARE db_cursor CURSOR FOR
SELECT mail, mailBody FROM @mailAgg

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO  @mail, @mailBody

WHILE @@FETCH_STATUS = 0  
BEGIN
    -- 直接调用发送邮件存储过程，无需额外查询
    EXEC msdb.dbo.sp_send_dbmail
        @recipients = @mail,
        @body = @mailBody,
        @body_format = 'HTML'

    FETCH NEXT FROM db_cursor INTO @mail, @mailBody
END

CLOSE db_cursor
DEALLOCATE db_cursor 

通用存储过程封装方案 #

如果需要支持动态传入不同的查询逻辑，可以结合动态SQL封装为通用存储过程：

CREATE PROCEDURE dbo.usp_SendBatchMail
    @querySQL nvarchar(max) -- 入参为公共查询SQL，固定返回mailBody、mailFooter、mail三个字段
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @mail varchar(255)=''
    DECLARE @mailBody nvarchar(max)=''
    DECLARE @mailData TABLE (
        mailBody nvarchar(max),
        mailFooter varchar(50),
        mail varchar(255)
    )
    -- 执行传入的动态查询，写入表变量
    INSERT INTO @mailData(mailBody, mailFooter, mail)
    EXEC sp_executesql @querySQL

    -- 聚合、发送逻辑和上文一致
    DECLARE @mailAgg TABLE (
        mail varchar(255),
        mailBody nvarchar(max)
    )
    INSERT INTO @mailAgg(mail, mailBody)
    SELECT 
        m.mail,
        (
            SELECT c.mailBody as 'span', c.mailFooter as 'small' 
            FROM @mailData c 
            WHERE c.mail = m.mail 
            FOR XML PATH ('div'), TYPE
        ).value('.', 'nvarchar(max)') as mailBody
    FROM @mailData m
    GROUP BY m.mail

    DECLARE db_cursor CURSOR FOR
    SELECT mail, mailBody FROM @mailAgg

    OPEN db_cursor
    FETCH NEXT FROM db_cursor INTO  @mail, @mailBody

    WHILE @@FETCH_STATUS = 0  
    BEGIN
        EXEC msdb.dbo.sp_send_dbmail
            @recipients = @mail,
            @body = @mailBody,
            @body_format = 'HTML'

        FETCH NEXT FROM db_cursor INTO @mail, @mailBody
    END

    CLOSE db_cursor
    DEALLOCATE db_cursor 
END

调用示例：

DECLARE @sql nvarchar(max) = N'
select ''mail body content-1'' as ''mailBody'',''11/9/221'' as ''mailFooter'',''a@a.com'' as ''mail''
union all
select ''mail body content-2'' as ''mailBody'',''11/09/2021'' as ''mailFooter'',''b@b.com'' as ''mail''
union all
select ''mail body content-3'' as ''mailBody'',''10/09/2021'' as ''mailFooter'',''a@a.com'' as ''mail''
'
EXEC dbo.usp_SendBatchMail @querySQL = @sql

• STRING_AGG：SQL Server 2016不支持该函数（2017及以上版本才可用），不适配你的场景
• 条件循环：实现复杂度高于临时表方案，没有必要采用
• sp_execute：仅在需要封装通用存储过程、动态传入查询逻辑时使用，普通场景不需要额外引入动态SQL的复杂度

你原有的按收件人分组、单独发送邮件的逻辑是合理的，符合业务需求，仅存在重复代码的冗余问题，用上述方案优化即可。

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