SQL Table of Results in an HTML email
Published: Oct 03, 2014
To be honest I couldn’t think of a good title for this particular post so I’ve gone with the above. Basically what I’m referring to is that sometimes a simple text email isn’t enough and you want to include some output with it… therefore what you need is a table.

Creating a table in an email body is not the simplest of things and I’ve seen numerous people struggle to create one, ending up doing something incredibly complex with their output, or even trying external plugins.

Luckily it’s not that difficult if you use a little XML. To clarify at this point, I’m not very good with XML myself and therefore you don’t have to have an in depth knowledge to do this, in fact it’s really quite simple to manipulate.

In this example I’ll create a simple table and then put it into an email. Once you know the technique it’s easy to apply to all manner of alerts and reporting you may want to schedule. It does look VERY complicated at first glance, but once you understand it, it’s really not.

Let’s start with a simple table and a couple of lines of data:

if object_id('tempDB..myEmailTable') is not null drop table myEmailTable
go

create table myEmailTable
(
  
id tinyint identity(1, 1),
  
firstName varchar(15),
  
lastName varchar(20),
  
isAdult bit
)
go

insert into myEmailTable
values('Homer', 'Simpson', 1),
       (
'Marge', 'Simpson', 1),
       (
'Bart', 'Simpson', 0),
       (
'Lisa', 'Simpson', 0),
       (
'Maggie', 'Simpson', 0)
go


Now we have our data, let’s presume that we would like to receive an email each day which lets us know who the Adults are in our table. (I’ve no idea why this would be useful, but it’s just an example so we’ll go with it)

The way to achieve this would be as follows:

declare @emailSubject varchar(100),
      
@textTitle varchar(100),
      
@columnHeaders varchar(1000),
      
@tableHTML nvarchar(max)

select @emailSubject = 'My Test Email',
      
@textTitle = 'All adults in #myEmailTable'

select @columnHeaders = isnull(@columnHeaders + '</th><th>', '') + c.name
from sys.objects o
join sys.columns c
on o.object_id = c.object_id
where o.name = 'myEmailTable'
and c.name != 'id' -- Because I don't want this column in the output
order by c.column_id

set @tableHTML =
  
'<div><b>Database Disk Usage:</b></div><br>' +
  
'<table border="1" cellpadding="5"><font face="Calibri" size=2>' +
  
'<tr><th>' + @columnHeaders + '</th></tr>' +
  
convert(nvarchar(max),
   (
      
select td = firstName, '',  -- Here we put the column names
              
td = lastName, '',  -- Here we put the column names
              
td = isAdult, ''    -- Here we put the column names
      
from myEmailTable
      
where isAdult = 1           -- Normal WHERE clause
      
order by id
      
for xml path('tr'), type
  
)) +
  
'</font></table>'
          
exec msdb.dbo.sp_send_dbmail
  
@profile_name = My SQL Mail Server',
  
@recipients = 'theBoredDBA@theBoredDBA.com',
  
@body = @tableHTML,
  
@subject = @emailSubject,
  
@body_format = 'HTML'


Hopefully you can see that this is quite simple to follow as it has clearly named variables and a couple of comments. Running this (obviously change your Mail Server name and recipient email address) should result in the following:



It’s really that simple. If you know any HTML then you can easily play with this a little and make it look a little more professional, but if not then look out for my next post as I’ll provide some formatting for you to tinker with.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron