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

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='[e-mail]',
    @subject = 'Work Order List',
    @body = @tableHTML,
    @body_format = 'HTML' ;

我有一个名为 Rating 的列，根据我自己的逻辑设置为好"或坏".我想让所有评级为坏"的行都有红色背景.我知道如何在 HTML 中执行此操作，但不确定如何使用此示例中演示的FOR XML"查询来执行此操作.似乎我必须为某些 TD 语句添加属性，而不是其他语句.

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.

推荐答案

你不能直接做.需要一点手工"HTML.这是一种可以使用的方法.

You can not do it directly.A little "Handcrafted" HTML is required. Here is an approach that can be used.

在单独的 CTE 中选择好记录和坏记录并附加td"标签.坏的也附加样式信息.

Select good and bad records in separate CTE and append the "td" tags. for bad ones append the Style information as-well.

然后附加tr"标签并组合 (UNION) 数据行并使用 for xmlpath 连接它们.

Then append "tr" tags and combine (UNION) data rows and concatenate them using for xmlpath.

为简单起见，我删除了按列排序，但您可以在 CTE 中选择它们并稍后对结果进行排序.

I have removed order by columns for simplicity but you can select them in the CTE and order the results later.

注意:我已经测试了输出 HTML，它可以工作，但我不是 HTML 人，所以不要介意 HTML 标签中是否有任何错误.请随时更正.

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='[e-mail]',
@subject = 'Work Order List',
@body = @tableHTML,
@body_format = 'HTML' ;

这篇关于sp_send_dbmail - 将表格中的行格式化为红色以发出警报的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！