SQL Server Result Set In An HTML Email

SQL Server Result Set In An HTML Email

The Problem

Picture the situation: you have a collection of data that you've been working on to summarise. You've finally found a way of displaying the data in an effective format, but you've overlooked one essential task: how are you going to relay this information back to the audience? You don't have a report server at hand, you're not all that great with SSIS and you're just looking for a quick way to display your result set by email to various recipients.

​Explanation & Requirements

I'm going to show you a great way to use a while loop to run through your result set and construct an HTML email. The good thing about this method is that once written, you can easily change the code snippet to fit your needs.

There's a few things you'll need to ensure are set up before we begin:

SQL Database Mail
Small Knowledge of Basic HTML/CSS
Some Sample Data


The Solution

I can help out with the sample data to use as an example. You can follow me with this and create the exact same model; if you decide to use your own data set; this will fully explain how to use it.

I'll use some random forenames and surnames. For the output I will aggregate the data and show how to make an enumerating column on the fly:

01.CREATE TABLE DBO.SAMPLEDATA (
02.FORENAME VARCHAR(50)
03.,SURNAME VARCHAR(50)
04.)
05. 
06.INSERT INTO DBO.SAMPLEDATA
07.SELECT 'JOHNNY', 'BELL'
08.INSERT INTO DBO.SAMPLEDATA
09.SELECT 'JOHNNY', 'BELL'
10.INSERT INTO DBO.SAMPLEDATA
11.SELECT 'JOHNNY', 'BELL'
12.INSERT INTO DBO.SAMPLEDATA
13.SELECT 'JOE', 'BLOGGS'
14.INSERT INTO DBO.SAMPLEDATA
15.SELECT 'JOE', 'BLOGGS'
16.INSERT INTO DBO.SAMPLEDATA
17.SELECT 'JOE', 'BLOGGS'
18.INSERT INTO DBO.SAMPLEDATA
19.SELECT 'JOHN', 'SMITH'
20.INSERT INTO DBO.SAMPLEDATA
21.SELECT 'JOHN', 'SMITH'
22.INSERT INTO DBO.SAMPLEDATA
23.SELECT 'JOHN', 'SMITH'
24.INSERT INTO DBO.SAMPLEDATA
25.SELECT 'JOHN', 'SMITH'


Now we have our data; we can create an aggregate table. I always dump these into temp tables, but you can decide for yourself. This includes an auto incremented column called "ID", this will be the column we use to "step over" the result set. A good tip is to always use the same order by statement in your row_number() declaration as you will for your result set, or the ID field will not be enumerating:

1.SELECT FORENAME, SURNAME, COUNT(*) AS RECORDS, ROW_NUMBER() OVER(ORDER BY COUNT(*) DESC) AS ID
2.INTO #TEMP
3.FROM DBO.SAMPLEDATA
4.GROUP BY FORENAME, SURNAME
5.ORDER BY COUNT(*) DESC


Now we have our result set, we can go ahead and build the T-SQL query that will generate our HTML email. I'll post the whole thing for readability, then afterwards I will explain my logic:

01.DECLARE @ID INT
02.DECLARE @MAX INT
03.DECLARE @HTML NVARCHAR(4000)
04.DECLARE @FORENAME VARCHAR(50)
05.DECLARE @SURNAME VARCHAR(50)
06.DECLARE @RECORDS NVARCHAR(10)
07. 
08.SET @HTML = '<body><table><tr><td>Forename</td><td>Surname</td><td>Records</td></tr>'
09. 
10.SELECT @MAX = MAX(ID) FROM #TEMP
11. 
12.SET @ID=1
13.WHILE @ID <= @MAX
14.BEGIN
15.SELECT @FORENAME = FORENAME FROM #TEMP WHERE @ID = ID
16.SELECT @SURNAME = SURNAME FROM #TEMP WHERE @ID = ID
17.SELECT @RECORDS = RECORDS FROM #TEMP WHERE @ID = ID
18. 
19.SET @HTML = @HTML + '
20.<tr><td>'+@FORENAME+'</td><td>'+@SURNAME+'</td><td>'+@RECORDS+'</td></tr>'
21.  
22.SET @ID = @ID + 1
23.END
24.  
25.SET @HTML = @HTML + '</table></body>'


So first we declare our variables. @ID is used within the while loop to increment until all rows have been implemented into the HTML statement. @MAX gets the last row of the result set. @HTML is the variable used to store the HTML statement, this is set to 4000 to ensure it doesn't overflow and truncate. We then declare each column name within a variable as a "node" to store our values temporarily as per each row within the result set.

If I print the @HTML variable to my console; it shows this:-

1.<body><table><tr><td>Forename</td><td>Surname</td><td>Records</td></tr>
2.<tr><td>JOHN</td><td>SMITH</td><td>4</td></tr>
3.<tr><td>JOHNNY</td><td>BELL</td><td>3</td></tr>
4.<tr><td>JOE</td><td>BLOGGS</td><td>3</td></tr></table></body>


The last step in this method is to email your result set. This is pretty straight forward using a system stored procedure called sp_send_dbmail.

1.EXECUTE Msdb..sp_send_dbmail   
2.@PROFILE_NAME = 'DATA'
3.,@RECIPIENTS = 'johnny.bell@test-email.co.uk'
4.,@BODY = @HTML
5.,@SUBJECT = 'Forenames and Surnames'
6.,@BODY_FORMAT = 'HTML'


Summary

Voila! Your chosen recipient(s) will receive the result-set in a nice neat HTML email. An important thing to note is that any CSS formatting you wish to do to an HTML email in SQL Server will need to be at INLINE level. Unfortunately other methods are not supported.

I hope this article helps you, if you have any questions. Do not hesitate to ask.

See also:
How I use PowerShell to generate table output for blog posts from query results

         and

Output table as HTML table

for different solutions of this problem.

This entry participates in the TechNet Guru contributions for June contest.

Leave a Comment
  • Please add 2 and 5 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • Naomi  N edited Revision 6. Comment: Added new tags, See also and last line. Corrected some typos

  • Ed Price - MSFT edited Revision 5. Comment: Great article! Removed period from title (per guidelines). Added tags.

Page 1 of 1 (2 items)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
  • Ed Price - MSFT edited Revision 5. Comment: Great article! Removed period from title (per guidelines). Added tags.

  • Naomi  N edited Revision 6. Comment: Added new tags, See also and last line. Corrected some typos

  • Is there a reason why the table is defined using VARCHAR(50), but we later use VARCHAR(150) in the HTML?

  • No Richard. No reason, just inconsistent DDL on my part. I'll go ahead and correct it now.

  • Congratulations on winning a medal! blogs.technet.com/.../technet-guru-awards-june-2013.aspx

  • Hi Johnny, Thanks for the detailed Article. I just want to confirm something. You mention that: "any CSS formatting you wish to do to an HTML email in SQL Server will need to be at INLINE level".

    I want to specify <td> width, but it is not supported in HTML5 and the recommendation is to use CSS? What do you suggest?

  • Hi Terence,

    Usually because of the variable length of data contained within a column, I like to specify a fixed width with an NCHAR variable within the result set, this way HTML will wrap the element around each variable automatically. I understand this would be favourable at application/front end level, but it seems that SQL Server compatibility with HTML is quite limited.

    Where I declare the holding variables I would change the type from VARCHAR and NVARCHAR to NCHAR and CHAR with a suitable length to hold the data.

    Hope this helps!

  • Hi Terence,

    Usually because of the variable length of data contained within a column, I like to specify a fixed width with an NCHAR variable within the result set, this way HTML will wrap the element around each variable automatically. I understand this would be favourable at application/front end level, but it seems that SQL Server compatibility with HTML is quite limited.

    Where I declare the holding variables I would change the type from VARCHAR and NVARCHAR to NCHAR and CHAR with a suitable length to hold the data.

    Hope this helps!

  • Hi Terence,

    Usually because of the variable length of data contained within a column, I like to specify a fixed width with an NCHAR variable within the result set, this way HTML will wrap the element around each variable automatically. I understand this would be favourable at application/front end level, but it seems that SQL Server compatibility with HTML is quite limited.

    Where I declare the holding variables I would change the type from VARCHAR and NVARCHAR to NCHAR and CHAR with a suitable length to hold the data.

    Hope this helps!

  • PERFECT! I got you on the first one hey LOL! Thanks for the feedback, I don't know why I didn't think of it, it is actually so simple.

Page 1 of 1 (10 items)