asked on

Trying to make alternate lines in an html table generated by sp_send_dbmail have a different background color

I am trying to get the HTML table generated in an email to have alternating background colors for each row. The HTML table/email are being generated in SQL Server 2005 via the command sp_send_dbmail. Attached is a example code of what I am doing currently from SQL Server Books Online. Any help is appreciated thanks in advance.

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

Open in new window

chapmandew

I guess you could use the ranking function row_number() and do a modulus operation (%2) on it to make the even numbers one color and the odds another...make sense?

SomeGuy892

ASKER

Thanks for the quick response chapmandew. So that gives me something to use in a conditional statement to determine the row background color however it doesnt create the color itself. That is the part that has me stuck. Additionally I will now have to add the html tag of "style='visibility: hidden' " to the td that returns the 0/1 flag. Unless someone has a better idea of how to ensure that 0 / 1 is not returned in the email that is generated

Here is an untested copy of what we have come up with so far:

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
td = ROW_NUMBER() OVER (ORDER BY DueDate ASC,(p.ListPrice - p.StandardCost) * wo.OrderQty DESC) % 2
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' ;

chapmandew

Can you throw some logic in there so that you set the bgcolor?

maybe something like this:

select case ranking % 2 when 1 then 'bgcolor = "silver" else 'bgcolor = "gold"' from
(
select *, ranking = ROW_NUMBER() OVER (ORDER BY DueDate ASC,(p.ListPrice - p.StandardCost) * wo.OrderQty DESC) from tablename
)a

SomeGuy892

ASKER

chapmandew
I believe that I need to get the bgcolor attribute inside of the <tr> which is generated by the query. So what I am looking to have generated is <tr bgcolor="green">. If I can just figure out how to set the background color for the row then I could apply a case statement similar to what you have and get the background color to alternate for each row. But since the <tr> is generated via XML I think?? I have not been able to do it. I have tried a case statement similar to what you have and I can get bgcolor="green" to be returned in the results just not within the <tr> to get the row to change color properly. I have been pounding on this for a while with no luck so any help is appreciated.

chapmandew

Let me play w/ it a bit...

chapmandew

this is a nasty one....

SomeGuy892

ASKER

Yes it sure seems to be although I know there has to be an easy answer somewhere. Here is a link to a related issue in another forum that might provide some clue to you. http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=96536

ASKER CERTIFIED SOLUTION

SomeGuy892

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial