Generating and emailing HTML reports from SQL Server

Recently, I was looking for a way to email nightly reports directly from SQL Server (I'm using 2008 R2). With sp_send_dbmail, sending email is a piece of cake after setting up your mailing profile. But ever try attaching a query? You get a disgusting regurgitation of your results splattered all over your email. It's not pretty.... or readable.

So I created a stored procedure to convert a temp table into an HTML table. Yep, just select something into a temp table and call this stored procedure and out pops an HTML table suitable for emailing. Here's the code:



CREATE procedure [dbo].[temp_table_to_html](@tbl varchar(64), @no_results_msg varchar(MAX) = 'None') as begin
  set NOCOUNT ON
  
  if exists (select * from tempdb.sys.columns where object_id = object_id('tempdb..#'+@tbl)) begin
    declare @q varchar(MAX);
    declare @cols varchar(MAX);
    declare @c int;
    set @c = 0;
    create table #c (c int);
    set @q = 'insert into #c select count(0) from #'+@tbl
    exec(@q)
    select @c = c from #c;
    drop table #c;
    
    if @c > 0 begin 
        select '<style>table,td,th { border:solid 1px #ccc; border-collapse:collapse; padding: 3px;}</style>';
select '<table><tr>';
select '<th>'+name+'</th>' from tempdb.sys.columns where object_id = object_id('tempdb..#'+@tbl);
select '</tr>';
   
select @cols = coalesce(@cols +'+','') + '''<td>''+ISNULL(cast('+name+' as varchar(MAX)),''NULL'')+''</td>''' from tempdb.sys.columns where object_id = object_id('tempdb..#'+@tbl);
   
   
set @q = 'select ''<tr>''+' + @cols + ' +''</tr>'' from #'+@tbl;
   
exec sp_sqlexec @q
select '</tr>'
   
select '</table>';
end
if @c is null or @c = 0 select @no_results_msg as NoRows
  end


  set NOCOUNT OFF
end



And here is how you might use it:


select 'Joe' as Name, 25 as Age, 42.10 as Budget into #results
exec dbo.temp_table_to_html 'results'

It returns multiple tables that are all chunks of HTML. Htmm... not pretty in SQL Server Management Studio.

So lets add a switch to control whether it renders HTML or returns a table and we get the best of both worlds.

create procedure sample_report(@html bit = 0) as begin

  set nocount on

  select 'Joe' as Name, 25 as Age, 42.10 as Budget into #results;

  if @html is null set @html = 0;

  if @html = 0 begin

    select * from #results;

  end

  if @html = 1 begin

    exec dbo.temp_table_to_html 'results'

  end

  drop table #results;

  set nocount off

end

Now if we run the procedure manually it returns the query results as intended.

Ok, we've built our foundation. Now lets write a procedure to email the results of the query, as HTML.




exec msdb.dbo.sp_send_dbmail 
  @from_address = 'me@here.com',
  @recipients = 'you@there.com',
  @subject = 'That report you wanted',
  @body = '<h1>This appears before the HTML table and can be HTML as well.</h1><br/>',
  @query_result_header = 0,
  @execute_query_database = 'your_db_name_here',
  @query = 'exec dbo.sample_report 1',
  @body_format = 'HTML';




And that's it!  We can now email the results of a query very easily as a nice HTML table. Here's what our sample looks like: