Beautify HTML Tables in Emails for SQL Database Mail

By: Jeffrey Yao   |   Updated: 2021-07-13   |   Comments (3)   |   Related: > Database Mail

Problem

As a SQL Server DBA, I often use sp_send_mail to send various ad-hoc reports, quite a few of which are in tabular format with records less than 100 most of the time. Is there any way that we can beautify the report with the following two requirements:

1. Table rows are zebra-striped, i.e. odd numbered rows and even numbered rows have different background colors
2. One cell’s value can be of different color based on the value itself.

For example, I will use a similar table and data from the excellent tip by Bru Medishetty here at MSSQLTips.com, a regular tabular email format will be like the following:

But I’d like to see the following instead, we assume if [Ranking Points] is above 10000, the value is in green, and if the value is equal to or less than 5000, the value then should be in red.

How can I implement this with T-SQL?

Solution

You probably know that sp_send_dbmail can send mail with the body message in HTML format, and as such, we need to compose an HTML table, this means to fully understand the solution, we need some basic HTML knowledge, which is abundant online, and I will list one reference in the Next Steps section below.

To construct a concise and format rich HTML page, we need to use CSS (Cascading Style Sheet). We will use embedded CSS in our HTML composition for the database mail message.

I will basically use the same table in the original tip, but with two more records for demo purposes.

-- tested in sql server 2016 Developer Edition
-- modified from a tip of mssqltips.com
-- /sqlservertip/2347/send-email-in-a-tabular-format-using-sql-server-database-mail/
use master;
go
drop table if exists #Temp;
go
CREATE TABLE #Temp 
( 
  [Rank]  [int],
  [Player Name]  [varchar](128),
  [Ranking Points] [int],
  [Country]  [varchar](128)
)
go
INSERT INTO #Temp
SELECT 1,'Rafael Nadal',12390,'Spain'
UNION ALL
SELECT 2,'Roger Federer',7965,'Switzerland'
UNION ALL
SELECT 3,'Novak Djokovic',7880,'Serbia'
union all
select 4, 'George Yang', 5000, 'China'
union all
select 5, 'Peter Smith', 4000, 'Canada'
go

Algorithm

A plain HTML table generated using the original tip’s code will be like the following:

DECLARE @xmlNVARCHAR(MAX);
SET @xml= CAST((SELECT[Rank]AS'td','',[Player Name]AS'td','',[Ranking Points]AS'td','', CountryAS'td'
FROM #Temp
ORDERBY Rank
FOR XML PATH('tr'), ELEMENTS)ASNVARCHAR(MAX));
Select cast(@xml as xml)

The result is:

<tr>
  <td>1</td>
  <td>Rafael Nadal</td>
  <td>12390</td>
  <td>Spain</td>
</tr>
<tr>
  <td>2</td>
  <td>Roger Federer</td>
  <td>7965</td>
  <td>Switzerland</td>
</tr>
<tr>
  <td>3</td>
  <td>Novak Djokovic</td>
  <td>7880</td>
  <td>Serbia</td>
</tr>
<tr>
  <td>4</td>
  <td>George Yang</td>
  <td>5000</td>
  <td>China</td>
</tr>
<tr>
  <td>5</td>
  <td>Peter Smith</td>
  <td>4000</td>
  <td>Canada</td>
</tr>

Here are key points of the algorithm to generate an HTML table with requirements as mentioned in the [Problem] section:

1. We will count the string "<tr>" in the @xml variable, the even-numbered <tr> will be skipped while the odd-numbered <tr> will be replaced by <tr id= "odd">, here id="odd" is a CSS tag, which is defined in an embedded CSS, you will see it in the script below.
2. We will modify the SQL query so that if a column value is above a predefined threshold, let’s say 10000 in our case, the HTML code for this cell (like the value 12390) will be <td>zg12390</td> instead of the regular <td>12390</td>, for a cell whose value is less than or equal to 5000 (for example, in our case, there is a 4000 value), this will be <td>zr4000</td> instead of the regular string <td>4000</td>. With such adjustments, we can later further process the string to make it the exact HTML format we want, the details are in the [Script Explanation] section below.

Script Explanation

Now let’s take a look at the script. The script will first define embedded CSS part in the <style> … </style> section, which defines ID selectors for colors, #g for green, #r for red and #odd for lightgrey background color.

Next, the script will generate the core xml string into @xml, we will use a case statement to process the [Ranking Points] values according to our rules, i.e. if the value is <=5000, we put a special string "zr" in front of it, so when the @xml is generated, it will have the following format:

<td>zr5000</td>

Instead of the regular:

<td>5000</td>

If the value is >=10000, we put a special string "zg" in front it, and thus we will get the following string with our #temp data:

<td>zg12390</td>

Instead of the original:

<td>5000</td>

Later we will use a replace() function to replace <td>zr with <td id="r">, and replace <td>tg with <td id="g">, and so we will have:

<td id="r">5000</td>

and

<td id="g">12390</td>

After that, the script will start to count the <tr>, the first occurrence will be numbered as 1, the second will be 2 and so on, each occurrence will be counted in the @i variable, we use @i%2 to determine if this is an odd number or even number, if @i%2=1, it means this is an odd numbered line and it should have different background color, thus, we will replace <tr> with <tr id="odd">.

Here is the whole script

-- define CSS inside the HTML head section
declare @body varchar(max)= '
<html>
<head> <style>
 #g {color: green;}
 #r {color: red;}
 #odd {background-color: lightgrey}
</style> </head>';
 
declare @i int = 0, @pos int, @s varchar(max), @ts varchar(max), @xml VARCHAR(MAX);
declare @recipients varchar(256) = '[email protected]' -- change to your own
, @subject varchar(128) = 'Beautified DB Mail';
 
--get core xml string
SET @xml = CAST(( SELECT [Rank] AS 'td','',[Player Name] AS 'td','' 
 ,case when [Ranking Points] <=5000 then 'zr'+cast([Ranking Points] as varchar(30))
       when [Ranking Points] >=10000 then 'zg'+cast([Ranking Points] as varchar(30))
   else cast([Ranking Points] as varchar(30))
  end  AS 'td',''
, Country AS 'td'
FROM #Temp 
ORDER BY Rank 
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX));
 
set @xml=replace(@xml, '<td>zg', '<td id="g">'); -- highlight in Green
set @xml=replace(@xml, '<td>zr', '<td id="r">'); -- highlight in Red
 
select  @s = '', @pos = charindex('<tr>', @xml, 4);
 
while(@pos > 0)
begin
   set @i += 1;
   set @ts = substring(@xml, 1, @pos-1)
   if(@i % 2 = 1)
      set @ts = replace(@ts, '<tr>', '<tr id="odd">');
   set @s += @ts;
   set @xml = substring(@xml, @pos, len(@xml));
   set @pos =  charindex('<tr>', @xml, 4);
end -- while
-- handling the last piece
set @i +=1;
set @ts = @xml;
if(@i % 2 = 1)
   set @ts = replace(@ts, '<tr>', '<tr id="odd">');
set @s += @ts;
set @body +='<body> <H3>Tennis Rankings Info</H3> <table border=1>
      <tr><th>Rank</th><th>Player Name</th><th>Ranking Points</th><th>Country</th></tr>' 
+ @s+'</table> </body> </html>';
 
EXEC msdb.dbo.sp_send_dbmail
@recipients = @recipients, -- replace with your email address
@subject = @subject, 
@body = @body,
@body_format ='HTML';

DROP TABLE #Temp;

The generated HTML text in the @body variable is like the following:

<html>
<head> 
    <style>
    #g {color: green;}
    #r {color: red;}
    #odd {background-color: lightgrey}
  </style> 
  </head>
  <body> <H3>Tennis Rankings Info</H3>
   <table border=1>
      <tr>
          <th>Rank</th>
          <th>Player Name</th>
          <th>Ranking Points</th>
          <th>Country</th>
      </tr>
      <tr id="odd">
           <td>1</td>
           <td>Rafael Nadal</td>
           <td id="g">12390</td>
           <td>Spain</td>
      </tr>
      <tr>
           <td>2</td>
           <td>Roger Federer</td>
           <td>7965</td>
           <td>Switzerland</td>
      </tr>
      <tr id="odd">
           <td>3</td>
           <td>Novak Djokovic</td>
           <td>7880</td>
           <td>Serbia</td>
      </tr>
      <tr><td>4</td>
           <td>George Yang</td>
           <td id="r">5000</td>
           <td>China</td>
      </tr>
      <tr id="odd">
            <td>5</td>
           <td>Peter Smith</td>
           <td id="r">4000</td>
           <td>Canada</td>
      </tr>
  </table>
 </body>
</html>

Summary

In this tip, I demoed how to generate a zebra-striped tabular report and also based on the value in a cell, how to highlight the cell with a different color.

This rich-text style email mainly utilizes CSS to compose the required email, and such emails are much better in visualization and easier for call to attention specific details.

This tip can be considered the second part of the original tip, and as such, I just borrowed the same source data and script used in the original tip, but with small changes.

Next Steps

To make this more convenient to use, we can create a stored procedure which takes some parameters, such as a query string, a recipient parameter and a subject parameter, etc. and then sends out a customized email based on the result from the query. The design of the parameters is flexible as long as it meets your business needs, of course, there may be lots of dynamic T-SQL code in this SP.

The following references will be useful to understand database mail related topics and CSS:

• Send email in a tabular format using SQL Server database mail
• HTML Styles - CSS
• Setup SQL Server Database Mail to use a Gmail, Hotmail, or Outlook account
• Generate HTML Formatted Emails from SQL Server

About the author

Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more time for family, life and more automation.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips