SQL Server Result Set In An HTML Email

Article
1/17/2024

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. (Note: If you want to store more characters than 2000/4000 in a string variable, you will need to declare VARCHAR(MAX), but you won't be able to print this to console, the PRINT function is ALWAYS limited to 4000 characters.) 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.

and

Output table as HTML table

for different solutions of this problem.

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

Share via

SQL Server Result Set In An HTML Email

Additional resources