sp_send_dbmail - 将表格中的行格式化为警报的红色 [英] sp_send_dbmail - formatting row in table as red for an alert
问题描述
我在这个MSDN页面上使用类似于例子C的东西：
http://msdn.microsoft.com/en-us/library/ms190307.aspx
DECLARE @tableHTML NVARCHAR（MAX）;
SET @tableHTML =
N'< H1>工作订单报表< / H1>'+
N'< table border =1>'+
N'tr< th>工作订单ID< th>产品ID>&$ b b′ b N< th>第< th> （（SELECT（））;第一个订单数量第二个订单数量第二个订单数量第二个订单数量第二个订单数量第二个订单数量第二个订单数量第二个订单数量第二个订单数量第二个订单td = wo.WorkOrderID，''，
td = p.ProductID，''，
td = p.Name，''，
td = wo.OrderQty，''，
td = wo.DueDate，''，
td =（p.ListPrice - p.StandardCost）* wo.OrderQty
FROM AdventureWorks.Production.WorkOrder as wo
JOIN AdventureWorks.Production .Product AS p
ON wo.ProductID = p.ProductID
WHERE DueDate>'2004-04-30'
AND DATEDIFF（dd，'2 004-04-30'，DueDate）<2
由DueDate ASC订购，
（p.ListPrice - p.StandardCost）* wo.OrderQty DESC
用于XML路径（'tr' ），TYPE
）AS NVARCHAR（MAX））+
N'< / table>';
EXEC msdb.dbo.sp_send_dbmail @ recipients ='danw @ Adventure-Works.com'，
@subject ='工作单列表'，
@body = @tableHTML，
@body_format ='HTML';
我有一列名为Rating的专栏，根据我自己设置为好或差逻辑。
我想让所有评分为坏的行都有红色背景。我知道如何在HTML中做到这一点，但不知道如何在本例中演示FOR XML查询。好像我不得不为某些TD语句添加属性，而不是其他属性。
你不能直接做这件事。需要一些手工制作的HTML。这是一个可以使用的方法。
在单独的CTE中选择好记录和差记录，并追加td标签。然后追加tr标签并合并（UNION）数据行，并将它们连接起来用于xmlpath。
为简单起见，我已经按列删除了订单，但您可以在CTE中选择它们并在稍后订购结果。
我已经测试了输出HTML，它可以工作，但我不是HTML家伙，所以不要介意HTML标记是否有任何错误。请随时纠正它。
DECLARE @tableHTML NVARCHAR（MAX）
，@ Data NVARCHAR（MAX）=''
SET @tableHTML =
N'< H1>工作单报告< />'+
N'< table border =1>'+
N'< tr>工作订单ID< th>产品ID'+
N'名称'第n个订单数量'第r个到期日期''+
N '< th>预期收入< / th>< / tr>'
; WITH CTE_Good AS
（
SELECT HTMLRowData = N < td>'STR（wo.WorkOrderID）+ N'< / td>'
+ N'td''+ STR（p.ProductID）+ N'< td>'
+ N'< td>'+ p.Name + N'< / td>'
+ N'< td>'STR（wo.OrderQty）+ N'< / td> ;'
+ N'< td>'+ CONVERT（VARCHAR（10），wo.DueDate，101）+ N'< td>'+ STR（（p.ListPrice - p.StandardCost）* wo.OrderQty）+ N'< / td>'
FROM AdventureWorks.Production.WorkOrder as wo
JOIN AdventureWorks.Production.Product AS p
ON wo.ProductID = p.ProductID
WHERE DueDate> '2004-04-30'
AND DATEDIFF（dd，'2004-04-30'，DueDate）< 2
--AND Rating ='Good'
）
，CTE_Bad AS
（
SELECT HTMLRowData = N'< td>< p style =color ：红色>'+ STR（wo.WorkOrderID）+ N'< / p>< / td>'
+ N'< td>< p style =color：red> '+ STR（p.ProductID）+ N'< / p>< / td>'
+ N'< td>< p style =color：red>'+ p.Name + N'< / p>< / td>'
+ N'< td>< p style =color：red>'+ STR（wo.OrderQty）+ N' / P>< / td>'
+ N'< td>< p style =color：red>'+ CONVERT（VARCHAR（10），wo.DueDate，101）+ N' < / p>< / td>'
+ N'< td>< p style =color：red>'+ STR（（p.ListPrice - p.StandardCost）* wo。 OrderQty）+ N'< / p>< / td>'
FROM AdventureWorks.Production.WorkOrder as wo
JOIN AdventureWorks.Production.Product AS p
ON wo.ProductID = p.ProductID
WHERE DueDate>'2004-04-30'
AND DATEDIFF（dd，'2004-04-30'，DueDate）< 2
--AND Rating ='Bad'
）
SELECT @ Data =（SELECT HTMLRows
FROM（
SELECT N' < tr>'+ HTMLRowData + N'< / tr>'AS HTMLRows FROM CTE_Bad $ b< tr>'HTMLRowData + N'< / tr>'AS HTMLRows FROM CTE_Good
UNION SELECT N' $ b）mi
用于XML路径（''），类型
）.value（'/'，'NVARCHAR（MAX）'）
SET @ tableHTML = @ tableHTML + @ Data + N'< / table>'
--SELECT @tableHTML
EXEC msdb.dbo.sp_send_dbmail @ recipients ='danw @ Adventure-Works.com' ，
@subject ='工作单列表'，
@body = @tableHTML，
@body_format ='HTML';
I'm using something similar to example C on this MSDN page: http://msdn.microsoft.com/en-us/library/ms190307.aspx
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H1>Work Order Report</H1>' +
N'<table border="1">' +
N'<tr><th>Work Order ID</th><th>Product ID</th>' +
N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' +
N'<th>Expected Revenue</th></tr>' +
CAST ( ( SELECT td = wo.WorkOrderID, '',
td = p.ProductID, '',
td = p.Name, '',
td = wo.OrderQty, '',
td = wo.DueDate, '',
td = (p.ListPrice - p.StandardCost) * wo.OrderQty
FROM AdventureWorks.Production.WorkOrder as wo
JOIN AdventureWorks.Production.Product AS p
ON wo.ProductID = p.ProductID
WHERE DueDate > '2004-04-30'
AND DATEDIFF(dd, '2004-04-30', DueDate) < 2
ORDER BY DueDate ASC,
(p.ListPrice - p.StandardCost) * wo.OrderQty DESC
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail @recipients='danw@Adventure-Works.com',
@subject = 'Work Order List',
@body = @tableHTML,
@body_format = 'HTML' ;
I have a column called Rating that is set to 'Good' or 'Bad' according to my own logic. I would like to make all lines that have a rating of 'Bad' have a red background. I know how to do it in HTML, but not sure how to do it with the "FOR XML" query being demonstrated in this example. Seems like I would have to add an attribute to some TD statements, and not others.
You can not do it directly.A little "Handcrafted" HTML is required. Here is an approach that can be used.
Select good and bad records in separate CTE and append the "td" tags. for bad ones append the Style information as-well.
Then append "tr" tags and combine (UNION) data rows and concatenate them using for xmlpath.
I have removed order by columns for simplicity but you can select them in the CTE and order the results later.
Note: I have tested the output HTML and it works but I am not HTML guy so don't mind if there is any mistake in HTML tags.feel free to correct it.
DECLARE @tableHTML NVARCHAR(MAX)
,@Data NVARCHAR (MAX)=''
SET @tableHTML =
N'<H1>Work Order Report</H1>' +
N'<table border="1">' +
N'<tr><th>Work Order ID</th><th>Product ID</th>' +
N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' +
N'<th>Expected Revenue</th></tr>'
;WITH CTE_Good AS
(
SELECT HTMLRowData= N'<td>'+STR(wo.WorkOrderID)+N'</td>'
+N'<td>'+STR(p.ProductID)+N'</td>'
+N'<td>'+p.Name+N'</td>'
+N'<td>'+STR(wo.OrderQty)+N'</td>'
+N'<td>'+CONVERT(VARCHAR(10),wo.DueDate,101)+N'</td>'
+N'<td>'+STR((p.ListPrice - p.StandardCost) * wo.OrderQty)+N'</td>'
FROM AdventureWorks.Production.WorkOrder as wo
JOIN AdventureWorks.Production.Product AS p
ON wo.ProductID = p.ProductID
WHERE DueDate > '2004-04-30'
AND DATEDIFF(dd, '2004-04-30', DueDate) < 2
--AND Rating = 'Good'
)
,CTE_Bad AS
(
SELECT HTMLRowData= N'<td><p style="color:red">'+STR(wo.WorkOrderID)+N'</p></td>'
+N'<td><p style="color:red">'+STR(p.ProductID)+N'</p></td>'
+N'<td><p style="color:red">'+p.Name+N'</p></td>'
+N'<td><p style="color:red">'+STR(wo.OrderQty)+N'</p></td>'
+N'<td><p style="color:red">'+CONVERT(VARCHAR(10),wo.DueDate,101)+N'</p></td>'
+N'<td><p style="color:red">'+STR((p.ListPrice - p.StandardCost) * wo.OrderQty)+N'</p></td>'
FROM AdventureWorks.Production.WorkOrder as wo
JOIN AdventureWorks.Production.Product AS p
ON wo.ProductID = p.ProductID
WHERE DueDate > '2004-04-30'
AND DATEDIFF(dd, '2004-04-30', DueDate) < 2
--AND Rating = 'Bad'
)
SELECT @Data=(SELECT HTMLRows
FROM (
SELECT N'<tr>'+HTMLRowData+N'</tr>' AS HTMLRows FROM CTE_Good
UNION SELECT N'<tr>'+HTMLRowData+N'</tr>' AS HTMLRows FROM CTE_Bad
) mi
FOR XML PATH(''), TYPE
).value('/', 'NVARCHAR(MAX)')
SET @tableHTML=@tableHTML+@Data+N'</table>'
--SELECT @tableHTML
EXEC msdb.dbo.sp_send_dbmail @recipients='danw@Adventure-Works.com',
@subject = 'Work Order List',
@body = @tableHTML,
@body_format = 'HTML' ;
这篇关于sp_send_dbmail - 将表格中的行格式化为警报的红色的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
