Email Formatted HTML Table with T-SQL

June 21, 2013 § 3 Comments

This is life savior stuff. As we send HTML formatted email. Sharing Steve Moore articles below

Email Formatted HTML Table with T-SQL

By Steve Moore, 2013/06/20

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)

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.

Advertisements

§ 3 Responses to Email Formatted HTML Table with T-SQL

  • Marvin says:

    I just couldn’t depart your site before suggesting that I really enjoyed the standard information a person provide for your visitors? Is gonna be back frequently in order to investigate cross-check new posts

  • I must thank you for the efforts you have put in writing this site.
    I am hoping to see the same high-grade content from you in the future as well.

    In fact, your creative writing abilities has inspired me
    to get my own website now 😉

  • Alexander says:

    Very good blog! Do you have any recommendations for aspiring writers?
    I’m hoping to start my own website soon but I’m a little lost on everything.
    Would you propose starting with a free platform like WordPress or go for
    a paid option? There are so many options out there that I’m totally confused .. Any recommendations? Thanks!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

What’s this?

You are currently reading Email Formatted HTML Table with T-SQL at Naik Vinay.

meta

%d bloggers like this: