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

Question

I am running a SQL query that returns a table of results. I want to send the table in an email using dbo.sp_send_dbMail.

Is there a straightforward way within SQL to turn a table into an HTML table? Currently, I'm manually constructing it using COALESCE and putting the results into a varchar that I use as the emailBody.

Is there a better way to do this?

Answer 1

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.

Answer 2

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.