Email Formatted HTML Table with T-SQL

,

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)