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