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))
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:
Advertisement
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';