Sending a HTML based on SQL server table with conditional formatting

I can send a mail based upon a table in SQL. However, when it comes to changing color on a cell based upon a certain value (conditional formatting). I just cant get it to work. I’ve searched on here and can’t find anything based upon a field value.

A segment of my code is as follows:

    SELECT 
    ID,
    [Name],
    CONVERT(VARCHAR(MAX), [Total]) AS [Total],
    [Qty],
    CASE WHEN [Qty] <25 THEN 'Red'
    WHEN [Qty] BETWEEN 25.1 AND 40 THEN 'Orange'
    ELSE 'OK' END AS [Check]
      INTO #Mail  FROM [volume].[dbo].[Totals]
    
    
    DECLARE @xml NVARCHAR(MAX)
    DECLARE @body NVARCHAR(MAX)
    
    SET @xml = CAST(( SELECT [ID] AS 'td','',[Name] AS 'td','', [Total] AS 'td','',  TD = CASE WHEN [Check] = 'Red' THEN N'<font color="red">[Qty]</font>' WHEN [Check] = 'Orange' THEN N'<font color="orange">[Qty]</font>' ELSE N'<font color="green">[Qty]</font>' END 
    FROM #MAIL
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

​x
 
    SELECT     ID,    [Name],    CONVERT(VARCHAR(MAX), [Total]) AS [Total],    [Qty],    CASE WHEN [Qty] <25 THEN 'Red'    WHEN [Qty] BETWEEN 25.1 AND 40 THEN 'Orange'    ELSE 'OK' END AS [Check]      INTO #Mail  FROM [volume].[dbo].[Totals]            DECLARE @xml NVARCHAR(MAX)    DECLARE @body NVARCHAR(MAX)        SET @xml = CAST(( SELECT [ID] AS 'td','',[Name] AS 'td','', [Total] AS 'td','',  TD = CASE WHEN [Check] = 'Red' THEN N'<font color="red">[Qty]</font>' WHEN [Check] = 'Orange' THEN N'<font color="orange">[Qty]</font>' ELSE N'<font color="green">[Qty]</font>' END     FROM #MAILFOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))​​

When I try the above it sends the Case statement as free text not picking up the HTML and the DB Value. I would be grateful in some guidance.

The output I’m getting by the above is:

Answer

drop table if exists #Mail;

SELECT 
    ID,
    [Name],
    CONVERT(VARCHAR(MAX), [Total]) AS [Total],
    [Qty],
    CASE WHEN [Qty] <25 THEN 'Red'
    WHEN [Qty] BETWEEN 25.1 AND 40 THEN 'Orange'
    ELSE 'OK' END AS [Check]
    INTO #Mail  FROM --[volume].[dbo].[Totals]
    (values (1, 'Name1', 100, 20),  (2, 'Name2', 200, 30),  (3, 'Name3', 300, 50)) as Totals(Id,Name,Total,Qty);
    
declare @xml nvarchar(max) = (
select 
    ID as 'td', 
    '',
    Name as 'td',
    '',
    Total as 'td',
    '', 
    case [Check] 
        when 'Red' then 'red'
        when 'Orange' then 'orange'
        else 'green'
    end as 'td/font/@color',
    QTY as 'td/font'
FROM #MAIL  
FOR XML PATH('tr') 
);


declare @body nvarchar(max) = N'
<html>
<head>
<meta name="ProgId" content="Word.Document"/> <!-- for MSOutlook -->
<style>

.table {
border-collapse: collapse;
width: 100%;
}

.th, .td{
font-size:11.0pt;
font-family:"Calibri",sans-serif;
padding: 8px;
text-align: left;
border-bottom: 1px solid #dddddd;
}
</style></head><body>
<table>
<tr><th>Id</th><th>Name</th><th>Total</th><th>Qty</th></tr>'
+ @xml + N'
</table>
</body></html>';



--send an email
EXEC msdb.dbo.sp_send_dbmail  
    --@profile_name = 'myprofile', --if no public profile
        @recipients = 'putemailhere@emailxyz.com', --<-- recipients
        @copy_recipients = null,
        @from_address = 'sql server<sqlserver@planetearth.org>',
        @reply_to = 'sqlserver@planetearth.org',
        @subject = 'test report',
        @body = @body,
        @body_format = 'HTML';

 
drop table if exists #Mail;​SELECT     ID,    [Name],    CONVERT(VARCHAR(MAX), [Total]) AS [Total],    [Qty],    CASE WHEN [Qty] <25 THEN 'Red'    WHEN [Qty] BETWEEN 25.1 AND 40 THEN 'Orange'    ELSE 'OK' END AS [Check]    INTO #Mail  FROM --[volume].[dbo].[Totals]    (values (1, 'Name1', 100, 20),  (2, 'Name2', 200, 30),  (3, 'Name3', 300, 50)) as Totals(Id,Name,Total,Qty);    declare @xml nvarchar(max) = (select     ID as 'td',     '',    Name as 'td',    '',    Total as 'td',    '',     case [Check]         when 'Red' then 'red'        when 'Orange' then 'orange'        else 'green'    end as 'td/font/@color',    QTY as 'td/font'FROM #MAIL  FOR XML PATH('tr') );​​declare @body nvarchar(max) = N'<html><head><meta name="ProgId" content="Word.Document"/> <!-- for MSOutlook --><style>​.table {border-collapse: collapse;width: 100%;}​.th, .td{font-size:11.0pt;font-family:"Calibri",sans-serif;padding: 8px;text-align: left;border-bottom: 1px solid #dddddd;}</style></head><body><table><tr><th>Id</th><th>Name</th><th>Total</th><th>Qty</th></tr>'+ @xml + N'</table></body></html>';​​​--send an emailEXEC msdb.dbo.sp_send_dbmail      --@profile_name = 'myprofile', --if no public profile        @recipients = 'putemailhere@emailxyz.com', --<-- recipients        @copy_recipients = null,        @from_address = 'sql server<sqlserver@planetearth.org>',        @reply_to = 'sqlserver@planetearth.org',        @subject = 'test report',        @body = @body,        @body_format = 'HTML';​

Advertisement

Answer