sp_send_dbmail中套用HTML样式

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

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

使用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会更具条件被解释为

<tr class='red'>或者<tr class='green'>

实现行样式

'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'		




评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值