You are currently viewing Sending HTML tables in mail from SQL Server
Microsoft SQL Server

Sending HTML tables in mail from SQL Server

This post contains a template for sending HTML tables via mail in T-SQL. A prerequisite is that you have created a Database Mail Profile. If you don’t have an email server available, you can use Gmail instead as demonstrated here.

The system stored procedure msdb.dbo.sp_send_dbmail has an @query parameter to include the results from a query in the body of the email. This method works fine for many simple queries. But sometimes I want more control over the look of the table, and that’s when I use HTML tables instead.

Let’s start with some sample data. Run the following script to create a database and table with some sample customer data:

CREATE DATABASE HTMLMailDemo
GO

USE HTMLMailDemo
GO

CREATE TABLE dbo.Customers
	(
		CustomerId INT IDENTITY(1,1) NOT NULL CONSTRAINT Customers_PK PRIMARY KEY CLUSTERED
		,FirstName VARCHAR(100) NOT NULL
		,LastName VARCHAR(100) NOT NULL
		,StreetAddress VARCHAR(100) NOT NULL
		,Phone VARCHAR(20) NULL
		,Added DATETIME2 NOT NULL
	)
GO

INSERT
	dbo.Customers (FirstName, LastName, StreetAddress, Phone, Added)
VALUES
	('Tomas', 'Lind', 'The Road 123', '111-222-333', '2015-06-24')
	,('Test', 'Testsson', 'Steet 45A ', '555-222-333', '2015-06-23')
	,('Bill', 'Bull', 'Old Town 999 abcdefghijklmnopqrstuvwxyz_abcdefghijklmnopqrstuvwxyz', NULL, '2015-06-22')
GO

SELECT * FROM dbo.Customers
HTML Email Demo Customers Table
HTML Email Demo Customers Table

Now, to send an email containing an HTML table with the customers above, use the following template:

DECLARE @MAIL_BODY VARCHAR(8000)

/* HEADER */
SET @MAIL_BODY = '<table border="1" align="center" cellpadding="2" cellspacing="0" style="color:black;font-family:consolas;text-align:center;">' +
	'<tr>
	<th>Customer Id</th>
	<th>First Name</th>
	<th>Last Name</th>
	<th>Street Address</th>
	<th>Phone</th>
	<th>Added</th>
	</tr>'

/* ROWS */
SELECT
	@MAIL_BODY = @MAIL_BODY +
		'<tr>' +
		'<td>' + CAST(CustomerId AS VARCHAR(11)) + '</td>' +
		'<td>' + FirstName + '</td>' +
		'<td>' + LastName + '</td>' +
		'<td>' + SUBSTRING(StreetAddress, 1, 30) + CASE WHEN LEN(StreetAddress) > 30 THEN '...' ELSE '' END + '</td>' +
		'<td>' + ISNULL(Phone, ' ... ') + '</td>' +
		'<td>' + CAST(CAST(Added AS DATE) AS VARCHAR(10)) + '</td>' +
		'</tr>'
FROM
	dbo.Customers

SELECT @MAIL_BODY = @MAIL_BODY + '</table>'

EXEC msdb.dbo.sp_send_dbmail
	@profile_name = <YourEmailProfile>,
	@recipients = <YourEmailRecipients>,
	@subject = 'Our Customers',
	@body = @MAIL_BODY,
	@body_format='HTML'

The actual HTML is stored in the @MAIL_BODY variable. First the initial table tag is created. Here are some properties that can be modified, such as font and border sizes.

Next, the table column headers are created (under the /* HEADER */ comment). Note the starting and closing tr (table row) tag.

The actual data is added with the next statement (under the /* ROWS */ comment). Starting and closing tr tags must be used. Note that all columns are casted to varchar (unless they already are varchar). For a normal string column, use for instance the FirstName column as template. To handle NULL values, use the Phone column template. A NULL value is replaced with three dots “…”. An empty string or whatever could also be used, but never return NULL values. Finally, the StreetAddress column shows a solution for only returning a subset of the characters in a column. The StreetAddress can be up to 100 characters, but in the code above only the first 30 are returned. If the string is shortened, “…” is appended to illustrate.

The final statement before sending the email is to close the HTML table with the /table tag.

Lastly, the email is sent using the msdb.dbo.sp_send_dbmail procedure. Remember to replace the YourEmailProfile and YourEmailRecipients placeholders.

Depending on your email client, the resulting email may look something like this:

HTML Mail Demo Customers Table In Mail
HTML Mail Demo Customers Table In Mail

If you want to alternate the font color per row, for instance to make the text red in case of error, replace the:

'<tr>' +

row above (line 17) with:

'<tr' + CASE WHEN CustomerId = 1 THEN ' style="color:red;">' ELSE ' style="color:black;">' END +

Replace with your own CASE statement.

Tomas Lind

Tomas Lind - Consulting services as SQL Server DBA and Database Developer at High Coast Database Solutions AB.

This Post Has 20 Comments

  1. Jignesh

    How come you have black background on your management studio?

  2. correy

    take a look at vsql-email (sql-email.com) this tool is exactly what I needed

  3. Ekrem Onsoy

    Thank you Tomas!

  4. Mrinal

    Hi I am getting below message. Can you help me out regarding this type conversion error

    1. Mrinal

      Msg 245, Level 16, State 1, Procedure automail_CustomerOrderStatusM, Line 73
      Conversion failed when converting the varchar value ” to data type int.

      1. Tomas Lind

        Hi, ” is a string and that can’t be converted to a integer. If it is possible, use a 0 (or some other int) instead. If not, change the data type to string. (Don’t know what the code does, it’s not from my code in the post, right?)

  5. Hunain Tariq

    How can i Convert this query into table:

    SELECT
    (select Categories.category from Categories where Categories.Categoryid = empattend.AttendenceType) AttendenceType,
    (select Categories.category from Categories where Categories.Categoryid = empattend.CheckoutFor) CheckoutFor,
    (select FirstName + ‘ ‘ + lastname from users where employeeid=empattend.EmployeeID) EmployeeName,
    COALESCE(empattend.CheckinTime,empattend.Checkouttime)as AttendanceTime,
    CAST(
    CASE
    WHEN empattend.CheckinTime IS not null and convert(time, empattend.CheckinTime)<'" + _LateCheckin+"' THEN 1 ELSE 0 END AS int) as late FROM EmployeeAttendance empattend LEFT JOIN EmployeeAttendance empattend2 ON (empattend.EmployeeId = empattend2.EmployeeId AND empattend.AttendanceID < empattend2.AttendanceID) WHERE empattend2. AttendanceID IS NULL and CONVERT(date, empattend.AttendanceDate)=CONVERT(date, getdate())

    It gives following error:
    Msg 102, Level 15, State 1, Line 4
    Incorrect syntax near '='.
    Msg 102, Level 15, State 1, Line 4
    Incorrect syntax near '+'.
    Msg 102, Level 15, State 1, Line 5
    Incorrect syntax near 'CheckoutFor'.
    Msg 102, Level 15, State 1, Line 6
    Incorrect syntax near 'EmployeeName'.

  6. Sam

    Great example, how to avoid not getting an email of no rows is returned? I tried your script, and it sends an email regardless. You have if @mail_body is not bull, but since we have set the @mail_body with the table header, there is always going to be a row.

  7. Sam

    Sorry Meant when no rows are returned… I was not paying attention.

  8. Sam

    I made a slight change to your query by adding a parm called it @MAIL_HDR, where I set the table definition there, and I placed the @MAIL_HDR after the checking for is not null returned from the select result.

    SET @MAIL_TBL = ” +

    tbl rows…

    /* ROWS */
    SELECT
    @MAIL_BODY =
    ” +
    ” + … the select code goes here
    from …
    if @MAIL_BODY is not null
    begin
    set @MAIL_BODY = @MAIL_TBL + @mail_BODY
    SELECT @MAIL_BODY = @MAIL_BODY + ”

    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = ’email_addr’,
    @recipients = ’email_addr’,
    @subject = @Subject,
    @body = @MAIL_BODY,
    @body_format=’HTML’
    end

    1. Tomas Lind

      Hi, thanks for the feedback and for providing a solution too! 🙂

  9. globaljj

    How would I show gridlines in the email body where the html tabular data is displayed? Is there a statement I need to add in my query ? Currently my results are populated in tabular format in the email body but the users require gridlines to be visible.

  10. prat

    how to get date with with datetime format in table ?

  11. Raj

    How can this code work ? doesn’t give below error
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, , >= or when the subquery is used as an expression.

  12. Raj

    This would only email the first row

  13. Dhilip

    What if my table has 0 rows. How will the email appear? I want the email to contain just the table headers with 0 rows whenever there is no data in the table and also with contents whenever there is data in the table. How to do that. Thanks!

  14. VC

    Hello, Trying to use this query, but getting only one row, the last row of a dataset. what am I doing wrong? – Thank you.

Leave a Reply