How to Conditionally format HTML TABLE in SQL SP

Question

I created a SP to monitor SQL Server Agent Jobs and generating Email for the same in HTML Format. However, I want to conditionally format the Run Status column to be Red/Green based on cell value(TEXT-Success/Fail). Please find the code at https://social.msdn.microsoft.com/Forums/sqlserver/en-US/2663e23b-0b55-4816-beb8-fdf8a020396d/formating-msdbdbospsenddbmail?forum=transactsql (Marked Answer by Hilary Cotter(MCC, MVP))

Kindly help me with the missing code to implement this feature.

Thank You.

Answer 1

Here's how you can modify the creation of the HTML to work:

DECLARE @tableHTML nvarchar(max);

SET @tableHTML = 
N'<style type="text/css">h2, body {font-family: Arial, verdana;}
table{font-size:11px; border-collapse:collapse;}
td{background-color:#F1F1F1; border:1px solid black; padding:3px;}
th{background-color:#99CCFF;}</style>' 
+ N'<h2>Recent Job Status</h2>'
+ N'<table border="1">' + N'<tr>
<th>Job Name</th>
<th>Last Run Date</th>
<th>Last Run Time</th>
<th>Last Run Status</th>'
+ CAST(( SELECT td = j.[name], 
                '', 
                td = MAX(jh.run_date), 
                '', 
                td = MAX(run_time),
                '', 
                td = CASE run_status 
                         WHEN 0
                         THEN N'<font color="red">Failure</font>'
                         WHEN 1
                         THEN N'<font color="green">Success</font>'
                         WHEN 2
                         THEN N'<font color="yellow">Retry</font>'
                         WHEN 3
                         THEN N'<font color="blue">Cancellation</font>'
                         WHEN 4
                         THEN N'<font color="black">In Progress</font>'
                         ELSE N'<font color="black">Unknown</font'
                    END
            FROM dbo.sysjobhistory AS jh
            INNER JOIN dbo.sysjobs AS j
            ON j.job_id = jh.job_id
            WHERE CONVERT(datetime, 
                          SUBSTRING(CONVERT(varchar(12), jh.run_date),
                                    0, 5) + N'-'
                  + SUBSTRING(CONVERT(varchar(12), jh.run_date), 5, 2) + '-'
                  + SUBSTRING(CONVERT(varchar(12), jh.run_date), 7, 2)) > DATEADD(day, -1, GETDATE())
            AND j.category_id <> 14
            GROUP BY j.[name], jh.run_status  
            FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX)) + N'</table>';
SET @tableHTML = REPLACE(REPLACE(@tableHTML, N'&lt;', N'<'), N'&gt;', N'>');

For my system, that then outputs this:

Hope that's what you need.