Wikis - Page Details

First published by Johnny Bell Jnr
When: 5 Jun 2013 5:12 AM
Last revision by Johnny Bell Jnr
When: 8 Jul 2013 3:40 PM
Revisions: 11
Comments: 10

Options

Can You Improve This Article?

Positively! Click Sign In to add the tip, solution, correction or comment that will help other users.

Report inappropriate content using these instructions.

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.