Email Formatted HTML Table with T-SQL

, 2015-08-28 (first published: )

One of the most common tasks for me is to send data from a query as the body of an e-mail.  You can do this in SSIS by outputting a query to a flat file, but there's no easy way to format the content.  You have the option of using XML and XSLT transformations, but that is a bit onerous for simple use cases. 

This article is about how to produce a table like the one below in an e-mail using just SQL.  The only real complexity is formatting alternate rows, which was my goal.  I wanted it to look like a .NET DataGrid (shown below).

The basic approach involves three steps:

  1. Create a query that pulls together the data as HTML.
  2. Use bcp and xp_cmdshell to write the SQL to a file.
  3. Email the file using Blat, a free SMTP client (www.blat.net)

EDIT:  Steps #2 and #3 are actually a bit redundant.  You can send the HTML in an email using sp_send_dbmail.  After creating the stored procedure below, procHtmlSql, you can send the SQL result by executing this SQL:

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'MYSERVER Email',
@recipients = 'foo@foo.com',
@subject = 'The Email Subject',
@query =N'EXECUTE MYDB.dbo.procHtmlSql',
@attach_query_result_as_file = 0,
@body_format = 'HTML';

One of the problems I ran into is the use of a <script> tag in the email, so there I had to abandon CSS styling and instead do inline styling on every row.

In order to find the odd/even rows, you need the ROW_NUMBER() function in a CTE, then use a CASE statement, and the modulo operator, to format the result:

CREATE PROCEDURE [dbo].[procHtmlSql] AS
BEGIN
SET NOCOUNT ON;
WITH htmlresult AS (
SELECT ROW_NUMBER() OVER (ORDER BY AuditTrail.EnteredDate) AS RowNumber,
'<td>' + AuditTrail.OldValue + '</td><td>' + AuditTrail.NewValue + '</td><td>'
+ AuditTrail.LoginID + '</td><td>'
+ CAST((CONVERT(smalldatetime, AuditTrail.EnteredDate)) AS VARCHAR(20)) + '</td>' AS HtmlData
FROM  AuditTrail
WHERE (AuditTrail.EnteredDate > GETDATE() - 1)
)
SELECT '<html><head><title></title></head>
    <body><br><br><table><tr style="background-color: #5D7B9D; font-weight: bold; color: white;">
    <td>Old Value</td><td>New Value</td><td>Login</td><td>Entered Date</td></tr>' AS HtmlData
UNION ALL
SELECT HtmlData =
    CASE RowNumber%2
        WHEN 0 THEN '<tr style="background-color: #F7F6F3">' + HtmlData + '</tr>'
        ELSE '<tr>' + HtmlData + '</tr>'
END
FROM htmlresult
UNION ALL
SELECT '</table></body></html>'
END

Once you have the SQL with HTML formatting, you want to use that within another stored procedure to write the result to a file.  This is what the second proc should look like:

CREATE PROC [dbo].[procHtmlFile] AS
BEGIN
   DECLARE @strCommand varchar(255)
   DECLARE @lret int
   SET @strCommand = 'bcp "EXECUTE MYDB.dbo.procHtmlSql" QUERYOUT C:\MyProject\SqlHtml.html -T -c'
   EXEC @lRet = master..xp_cmdshell @strCommand, NO_OUTPUT
END

Now you should be able to open the file "C:\MyProject\SqlHtml.html" in a browser and it should look similar to a .NET DataGrid.

Finally, send the file as the body of an e-mail with this procedure.

CREATE PROCEDURE [dbo].[procBlatSendMail]
  @to varchar(255) = 'foo@foo.com',   
  @from varchar(255) = 'foo@foo.com',
  @subject varchar(100) = 'My Example - '  
AS
  DECLARE @command as varchar(1500)
 
  SET @command = ''
  SET @command = @command + 'Blat C:\MyProject\SqlHtml.html'
  SET @command = @command + ' -to ' + '"' + @to + '"'
  SET @command = @command + ' -f ' + '"' + @from + '"'
  SET @command = @command + ' -subject ' + '"' + @subject + CONVERT(nvarchar(25), GETDATE()) + '"'
   
  EXEC master.dbo.xp_cmdshell @command

Simple, effective, and professional looking.

Rate

3.68 (56)

Share

Share

Rate

3.68 (56)

Related content

A Normalization Primer

For most DBAs, normalization is an understood concept, a bread and butter bit of knowledge. However, it is not at all unusual to review a database design by a development group for an OLTP (OnLine Transaction Processing) environment and find that the schema chosen is anything but properly normalized. This article by Brian Kelley will give you the core knowledge to data model.

5 (3)

2003-01-13

17,241 reads