sp_send_dbmail中套用HTML样式

最新推荐文章于 2021-06-17 20:09:14 发布

原创最新推荐文章于 2021-06-17 20:09:14 发布 · 1.3k 阅读

0 ·

CC 4.0 BY-SA版权

Database 专栏收录该内容

4 篇文章

订阅专栏

本文介绍如何利用SQL动态生成HTML邮件，并通过特定的SQL表达式为表格元素应用CSS样式，实现邮件中表格的自定义外观。

使用sp_send_dbmail发送HTML格式的邮件时，将某个列<td>套用Style

 SET @tableHTML = N'<HTML><HEAD> 
                         <style type="text/css">
                         th
                         {  height:30px;
                            width: 80px;
                            text-align:center;   
                         }
                         td
                         {  height:30px;
                            width: 80px;
                            text-align:left;
                            colspan="1";             
                         }
                         .amt{
			    text-align:right;
                         }     
                        </style>
                      </HEAD>
                    <BODY>' + @tableHead
                    + N'<table border="1" bordercolor="black" cellpadding="0" cellspacing="0" ><tr>'
                    + N'<th>公司别</th><th>报销日期</th><th class="no">表单单号</th><th>报销人</th><th>姓名</th><th>所在部门</th><th class="dept">部门名称</th>'
                    + N'<th>扣减部门</th><th  class="dept">部门名称</th><th class="content">摘要</th><th>科目</th><th>科目名称</th>'
                    + N'<th class="currency">币别</th><th>报销金额</th></tr>'
                    + CAST(( SELECT CASE WHEN amts>1000 THEN 'red' ELSE 'green' END AS '@class',
                                    td = company,
                                    '' ,
                                    td = paydate,
                                    '' ,
                                    td = payid,
                                    '' ,
                                    td = employeeid,
                                    '' ,
                                    td = employee,
                                    '' ,
                                    td = deptid,
                                    '' ,
                                    td = dept,
                                    '' ,
                                    td = factdeptid ,
                                    '' ,
                                    td = factdept,
                                    '' ,
                                    td = content,
                                    '' ,
                                    td = account,
                                    '' ,
                                    td = accdesc,
                                    '' ,
                                    td = currency,
                                    '' ,
                                    'amt' AS 'td/@class' ,
                                    CONVERT(DECIMAL(12, 2), amts) AS 'td'
                             FROM   sometable
                           FOR
                             XML PATH('tr') ,
                                 TYPE
                           ) AS NVARCHAR(MAX)) + N'</table><br><br>'
                    + @tableFoot + '</body></HTML>'

CASE WHEN amts>1000 THEN 'red' ELSE 'green' END AS '@class'

上面的SQL会更具条件被解释为

实现行样式

'amt' AS 'td/@class' ,
 CONVERT(DECIMAL(12, 2), amts) AS 'td'

上面的SQL会被解释为

<td class='amt'>[amts]</td>，实现针对某个列套用样式

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DatabaseMailProfile',
@recipients = @mailadd ,@copy_recipients = @copymailadd,
@subject = N'Mail Subject', @body = @tableHTML,
@body_format = 'HTML'