sp_send_dbmail - formatting row in table as red for an alert

Question

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='[email protected]',
    @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.

Solution

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

Comments

[Crazy Cucumber]

I know this question hadn't been active in 5 years, but if I wanted to add an ORDER BY to the end result, where should I add it? The variable @Data does not have any knowledge of the columns in either CTE. HTMLRowData is just one long string. I want to order by a column, say ProductID. Any help would be appreciated.