[MSSQL]SQL查询结果转换成HTML并通过邮件预警

来源：互联网发布：淘客cms系统是什么编辑：程序博客网时间：2024/05/16 05:18

上一篇文章讲解到配置数据库邮件实现发送邮件功能。

邮件的内容为HTML格式。
案例：

-- =============================================-- Author:      <Author,David Gong>-- Create date: <Create Date,2017-08-20,>-- Description: <Description,新增采购单无ROHS报告预警,>-- =============================================ALTER TRIGGER [dbo].[TR_NOROHS]    ON  [dbo].[PURTD]   AFTER INSERTAS BEGIN    -- SET NOCOUNT ON added to prevent extra result sets from    -- interfering with SELECT statements.    SET NOCOUNT ON;DECLARE @tableHTML  NVARCHAR(MAX) ;if exists(    SELECT      td = RTRIM(TC001), '',                    td = RTRIM(TC002), '',                    td = RTRIM(TD003), '',                    td = RTRIM(TC004), '',                    td = RTRIM(MA003), '',                    td = RTRIM(TD004), '',                    td = RTRIM(TD005), '',                    td = RTRIM(TD006), '',                    td = RTRIM(MV002), ''                                      from   inserted INNER JOIN PURTC on TD001=TC001 AND TD002=TC002INNER JOIN PURMA ON TC004=MA001INNER JOIN CMSMV ON TC011=MV001LEFT JOIN ROSH ON RTRIM(TC004)=Supplier AND  TD004=ItemWHERE ROSH.Item IS NULL             )beginSET @tableHTML =    N'<H1>无ROHS报告材料清单</H1>' +    N'<table border="1">' +    N'<tr><th>单别</th><th>单号</th><th>序号</th><th>供应商编码</th><th>供应商</th>' +    N'<th>品号</th><th>品名</th><th>规格</th><th>采购人员</th></tr>'+    CAST ( (     SELECT    td = RTRIM(TC001), '',                    td = RTRIM(TC002), '',                    td = RTRIM(TD003), '',                    td = RTRIM(TC004), '',                    td = RTRIM(MA003), '',                    td = RTRIM(TD004), '',                    td = RTRIM(TD005), '',                    td = RTRIM(TD006), '',                    td = RTRIM(MV002), ''                                      from   inserted INNER JOIN PURTC on TD001=TC001 AND TD002=TC002INNER JOIN PURMA ON TC004=MA001INNER JOIN CMSMV ON TC011=MV001LEFT JOIN ROSH ON RTRIM(TC004)=Supplier AND  TD004=ItemWHERE ROSH.Item IS NULL              FOR XML PATH('tr'), TYPE     ) AS NVARCHAR(MAX) ) +    N'</table>' ;    --print @tableHTMLEXEC msdb.dbo.sp_send_dbmail     @profile_name = 'MailETL',    @recipients='xx@zettlercn.com',    @blind_copy_recipients = 'xx@zettlercn.com' ,    @subject = '新增采购单无ROHS报告预警信息',    @body = @tableHTML,    @body_format = 'HTML' ; end    -- Insert statements for trigger hereEND

这里写图片描述

感谢小叶的帮助

阅读全文

1 0