Convert a SQL query result table to an HTML table for email

Here my common used script below. I use this for running scripts on two tables/views with SQL job and send results as two HTML tables via mail. Ofcourse you should create mail profile before run this.

DECLARE @mailfrom varchar(max)
DECLARE @subject varchar(100)
DECLARE @tableHTML NVARCHAR(MAX), @tableHTML1 NVARCHAR(MAX), @tableHTML2 NVARCHAR(MAX), @mailbody NVARCHAR(MAX)
DECLARE @Table1 NVARCHAR(MAX), @Table2 NVARCHAR(MAX)
DECLARE @jobName varchar(100)
SELECT @jobName = name from msdb..sysjobs where job_id = $(ESCAPE_NONE(JOBID))

-- If the result set is not empty then fill the Table1 HTML table
IF (SELECT COUNT(*) FROM [Database].[Schema].[Table1]) > 0
BEGIN
SET @Table1 = N''
SELECT @Table1 = @Table1 + '<tr style="font-size:13px;background-color:#FFFFFF">' + 
    '<td>' + ColumnText + '</td>' +
    '<td>' + CAST(ColumnNumber as nvarchar(30)) + '</td>' + '</tr>'  
FROM [Database].[Schema].[Table1]
ORDER BY ColumnText,ColumnNumber

SET @tableHTML1 = 
N'<table border="1" align="Left" cellpadding="2" cellspacing="0" style="color:black;font-family:arial,helvetica,sans-serif;text-align:left;" >' +
N'<tr style ="font-size:13px;font-weight: normal;background: #FFFFFF"> 
<th align=left>ColumnTextHeader1</th>
<th align=left>ColumnNumberHeader2</th> </tr>' + @Table1 + '</table>'
END
ELSE
BEGIN
SET @tableHTML1 = N''
SET @Table1 = N''
END


-- If the result set is not empty then fill the Table2 HTML table
IF (SELECT COUNT(*) FROM [Database].[Schema].[Table2]) > 0
BEGIN
SET @Table2 = N''
SELECT @Table2 = @Table2 + '<tr style="font-size:13px;background-color:#FFFFFF">' + 
    '<td>' + ColumnText + '</td>' +
    '<td>' + CAST(ColumnNumber as nvarchar(30)) + '</td>' + '</tr>'  
FROM [Database].[Schema].[Table2]
ORDER BY ColumnText,ColumnNumber

SET @tableHTML2 = 
N'<table border="1" align="Left" cellpadding="2" cellspacing="0" style="color:black;font-family:arial,helvetica,sans-serif;text-align:left;" >' +
N'<tr style ="font-size:13px;font-weight: normal;background: #FFFFFF"> 
<th align=left>ColumnTextHeader1</th>
<th align=left>ColumnNumberHeader2</th> </tr>' + @Table2 + '</table>'
END
ELSE
BEGIN
SET @tableHTML2 = N''
SET @Table2 = N''
END

SET @tableHTML = @tableHTML1 + @tableHTML2

-- If result sets from Table1 and Table2 are empty, then don't sent mail.
IF (SELECT @tableHTML) <> ''
BEGIN
SET @mailbody = N' Write mail text here<br><br>' + @tableHTML
SELECT @mailfrom = 'SQL Server <' + cast(SERVERPROPERTY('ComputerNamePhysicalNETBIOS') as varchar(50)) + '@domain.com>'
SELECT @subject = N'Mail Subject [Job: ' + @jobName + ']'
    EXEC msdb.dbo.sp_send_dbmail
    @profile_name= 'mailprofilename',
    @recipients= '<[email protected]>',
    @from_address = @mailfrom,
    @reply_to = '<[email protected]>',
    @subject = @subject,
    @body = @mailbody,
    @body_format = 'HTML'
--   ,@importance = 'HIGH'
END

Here is one way to do it from an article titled "Format query output into an HTML table - the easy way [archive]". You would need to substitute the details of your own query for the ones in this example, which gets a list of tables and a row count.

declare @body varchar(max)

set @body = cast( (
select td = dbtable + '</td><td>' + cast( entities as varchar(30) ) + '</td><td>' + cast( rows as varchar(30) )
from (
      select dbtable  = object_name( object_id ),
             entities = count( distinct name ),
             rows     = count( * )
      from sys.columns
      group by object_name( object_id )
      ) as d
for xml path( 'tr' ), type ) as varchar(max) )

set @body = '<table cellpadding="2" cellspacing="2" border="1">'
          + '<tr><th>Database Table</th><th>Entity Count</th><th>Total Rows</th></tr>'
          + replace( replace( @body, '&lt;', '<' ), '&gt;', '>' )
          + '</table>'

print @body

Once you have @body, you can then use whatever email mechanism you want.


This might give you some idea --

CREATE TABLE #Temp 
( 
  [Rank]  [int],
  [Player Name]  [varchar](128),
  [Ranking Points] [int],
  [Country]  [varchar](128)
)


INSERT INTO #Temp
SELECT 1,'Rafael Nadal',12390,'Spain'
UNION ALL
SELECT 2,'Roger Federer',7965,'Switzerland'
UNION ALL
SELECT 3,'Novak Djokovic',7880,'Serbia'


DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)


SET @xml = CAST(( SELECT [Rank] AS 'td','',[Player Name] AS 'td','',
       [Ranking Points] AS 'td','', Country AS 'td'
FROM  #Temp ORDER BY Rank 
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))


SET @body ='<html><body><H3>Tennis Rankings Info</H3>
<table border = 1> 
<tr>
<th> Rank </th> <th> Player Name </th> <th> Ranking Points </th> <th> Country </th></tr>'    


SET @body = @body + @xml +'</table></body></html>'


EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQL ALERTING', -- replace with your SQL Database Mail Profile 
@body = @body,
@body_format ='HTML',
@recipients = '[email protected]', -- replace with your email address
@subject = 'E-mail in Tabular Format' ;


DROP TABLE #Temp

I made a dynamic proc which turns any random query into an HTML table, so you don't have to hardcode columns like in the other responses.

-- Description: Turns a query into a formatted HTML table. Useful for emails. 
-- Any ORDER BY clause needs to be passed in the separate ORDER BY parameter.
-- =============================================
CREATE PROC [dbo].[spQueryToHtmlTable] 
(
  @query nvarchar(MAX), --A query to turn into HTML format. It should not include an ORDER BY clause.
  @orderBy nvarchar(MAX) = NULL, --An optional ORDER BY clause. It should contain the words 'ORDER BY'.
  @html nvarchar(MAX) = NULL OUTPUT --The HTML output of the procedure.
)
AS
BEGIN   
  SET NOCOUNT ON;

  IF @orderBy IS NULL BEGIN
    SET @orderBy = ''  
  END

  SET @orderBy = REPLACE(@orderBy, '''', '''''');

  DECLARE @realQuery nvarchar(MAX) = '
    DECLARE @headerRow nvarchar(MAX);
    DECLARE @cols nvarchar(MAX);    

    SELECT * INTO #dynSql FROM (' + @query + ') sub;

    SELECT @cols = COALESCE(@cols + '', '''''''', '', '''') + ''['' + name + ''] AS ''''td''''''
    FROM tempdb.sys.columns 
    WHERE object_id = object_id(''tempdb..#dynSql'')
    ORDER BY column_id;

    SET @cols = ''SET @html = CAST(( SELECT '' + @cols + '' FROM #dynSql ' + @orderBy + ' FOR XML PATH(''''tr''''), ELEMENTS XSINIL) AS nvarchar(max))''    

    EXEC sys.sp_executesql @cols, N''@html nvarchar(MAX) OUTPUT'', @html=@html OUTPUT

    SELECT @headerRow = COALESCE(@headerRow + '''', '''') + ''<th>'' + name + ''</th>'' 
    FROM tempdb.sys.columns 
    WHERE object_id = object_id(''tempdb..#dynSql'')
    ORDER BY column_id;

    SET @headerRow = ''<tr>'' + @headerRow + ''</tr>'';

    SET @html = ''<table border="1">'' + @headerRow + @html + ''</table>'';    
    ';

  EXEC sys.sp_executesql @realQuery, N'@html nvarchar(MAX) OUTPUT', @html=@html OUTPUT
END
GO

Usage:

DECLARE @html nvarchar(MAX);
EXEC spQueryToHtmlTable @html = @html OUTPUT,  @query = N'SELECT * FROM dbo.People', @orderBy = N'ORDER BY FirstName';

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Foo',
    @recipients = '[email protected];',
    @subject = 'HTML email',
    @body = @html,
    @body_format = 'HTML',
    @query_no_truncate = 1,
    @attach_query_result_as_file = 0;

Related: Here is similar code to turn any arbitrary query into a CSV string.