如何在SQL Server存储过程中将两个查询结果作为Excel附件邮件发送

sp_send_dbmail 单次调用仅支持通过@query参数生成1个查询结果附件，要实现同1封邮件附带2个查询结果附件，常用以下两种方案：

方案1：BCP导出临时文件后一次性发送（推荐，单封邮件双附件） #

该方案可以实现单封邮件附带两个独立的查询结果附件，是生产环境最常用的实现方式。

前提条件 #

• 运行SQL Server服务的账户对导出临时文件的目录拥有读写权限
• 符合安全规范的前提下启用xp_cmdshell

ALTER PROCEDURE [dbo].[EmailDailyMembers]
AS
BEGIN
    DECLARE @bcpCmd1 VARCHAR(8000)
    DECLARE @bcpCmd2 VARCHAR(8000)
    DECLARE @tempPath VARCHAR(200) = 'C:\SQLTemp\' -- 需提前在SQL Server服务器上创建该临时目录
    DECLARE @file1Path VARCHAR(200) = @tempPath + 'Report1.csv'
    DECLARE @file2Path VARCHAR(200) = @tempPath + 'Report2.csv'
    DECLARE @delCmd VARCHAR(8000)

    -- 先删除残留的旧临时文件
    SET @delCmd = 'del "' + @file1Path + '" /f & del "' + @file2Path + '" /f'
    EXEC master..xp_cmdshell @delCmd, no_output

    -- 导出第一个查询结果到CSV，和原格式保持一致用Tab作为分隔符
    SET @bcpCmd1 = 'bcp "USE Revenue2 SELECT * FROM [Membership Report]" queryout "' + @file1Path + '" -c -t\t -T -S' + @@SERVERNAME
    EXEC master..xp_cmdshell @bcpCmd1, no_output

    -- 导出第二个查询结果到CSV，替换为你自己的第二个查询语句即可
    SET @bcpCmd2 = 'bcp "USE Revenue2 SELECT * FROM [你的第二个查询表名]" queryout "' + @file2Path + '" -c -t\t -T -S' + @@SERVERNAME
    EXEC master..xp_cmdshell @bcpCmd2, no_output

    -- 发送邮件，同时附加两个临时文件
    EXEC msdb.dbo.sp_send_dbmail 
        @recipients = 'aaa@gmail.com',
        @subject = '今日双报表汇总', 
        @body = '附件是今日两份业务报表，请查收',
        @file_attachments = @file1Path + ';' + @file2Path, -- 多个附件路径用分号分隔即可
        @exclude_query_output = 1

    -- 发送完成后删除临时文件避免占用磁盘空间
    EXEC master..xp_cmdshell @delCmd, no_output
END

方案2：两次调用sp_send_dbmail（无需启用xp_cmdshell） #

如果不符合启用xp_cmdshell的安全要求，可以选择分两次发送邮件，每次附带一个查询结果附件：

ALTER PROCEDURE [dbo].[EmailDailyMembers]
AS
BEGIN
    DECLARE @query_result_separator CHAR(1) = char(9)

    -- 发送第一份报表邮件
    EXEC msdb.dbo.sp_send_dbmail 
        @recipients = 'aaa@gmail.com',
        @subject = '报表1：会员报告', 
        @query_attachment_filename = 'Report1.csv',  
        @attach_query_result_as_file = 1,
        @query_result_separator = @query_result_separator, 
        @query = 'USE Revenue2 SELECT * FROM [Membership Report]',
        @query_result_header = 1,
        @query_result_no_padding = 1,
        @exclude_query_output = 1

    -- 发送第二份报表邮件，替换查询语句和附件名即可
    EXEC msdb.dbo.sp_send_dbmail 
        @recipients = 'aaa@gmail.com',
        @subject = '报表2：业务统计报告', 
        @query_attachment_filename = 'Report2.csv',  
        @attach_query_result_as_file = 1,
        @query_result_separator = @query_result_separator, 
        @query = 'USE Revenue2 SELECT * FROM [你的第二个查询表名]',
        @query_result_header = 1,
        @query_result_no_padding = 1,
        @exclude_query_output = 1
END

注意事项 #

• 方案1使用的是Windows可信连接认证，如果你的SQL Server用SQL账户认证，需要把BCP命令里的-T参数替换为-U 用户名 -P 密码
• 临时目录必须是SQL Server服务器的本地路径，不能用客户端本地路径
• 如果需要两个查询结果放在同一个Excel的不同Sheet中，需要额外借助COM组件或者SSIS实现，复杂度更高

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