如何通过SQL存储过程发送带美化HTML表格的查询结果邮件

问题原因 #

你当前的写法中，sp_send_dbmail的@query返回结果会以纯文本形式追加到你写的HTML body末尾，自然无法渲染为表格。想要实现规整的表格展示，需要提前将SQL查询结果拼接为HTML表格字符串，再整合到邮件body中发送。

修正后的存储过程代码 #

ALTER PROCEDURE [dbo].[testEmail]
AS
BEGIN
    DECLARE @SubjectText VARCHAR(100) = 'Day ' + CONVERT(VARCHAR,(CONVERT(DATE,GETDATE())))
    -- 声明HTML相关变量
    DECLARE @TableHTML NVARCHAR(MAX),
            @Body NVARCHAR(MAX)

    -- 第一步：构建表格表头+基础样式
    SET @TableHTML = N'
    <table border="1" cellpadding="8" cellspacing="0" style="border-collapse:collapse;">
      <tr style="background-color:#f2f2f2;">
        <th>队伍ID</th>
        <th>队伍名称</th>
      </tr>'

    -- 第二步：拼接查询结果为表格行
    SET @TableHTML = @TableHTML + CAST((
        SELECT 
          td = IDDruzyny, '',
          td = nazwa, ''
        FROM TEST.dbo.Druzyna
        FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX)) + N'</table>'

    -- 第三步：整合完整邮件HTML内容
    SET @Body = N'
    <html>
    <body>
      <br/><b><font color=red>Hiho</font></b>
      <br/><br/>
      ' + @TableHTML + N'
    </body>
    </html>'

    -- 发送邮件，无需再传入@query相关参数
    EXEC msdb.dbo.sp_send_dbmail  
        @Body = @Body,                                                        
        @Body_format = 'HTML',                                                  
        @subject = @SubjectText,
        @profile_name = 'SIT',  
        @recipients = 'safemail@onet.eu'
END

关键说明 #

• 用FOR XML PATH('tr')可以快速将查询结果拼接为<tr>行标签包裹的格式，无需手动游标循环遍历数据，执行效率更高
• 表格样式直接写在内联属性中，适配绝大多数邮箱客户端的渲染规则，不会出现样式丢失
• 如果数据中包含<、>、&等特殊字符，XML拼接时会自动转义，不会破坏HTML结构
• 数据量较大时可以确认NVARCHAR(MAX)的容量是否满足需求，避免内容被意外截断
• 如果需要处理查询无数据的场景，可以提前判断表格行拼接结果是否为空，对应插入“暂无数据”的提示行即可

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