28 March 2008 20:19 tonyrogerson

Send Table or View as embedded HTML <table> in an email – stored procedure

Ever wanted to take a table, permanent or temporary or a view and just email it straight from SQL where the query result sits in the email as a nicely formatted HTML table rather than that ugly text file attachment you get when using sp_send_dbmail? Ever hit that infinite deadlock I blogged about the other day if you use the sp_send_dbmail attachment feature? Well, this stored procedure is for you!

This is an expansion of an example in the sp_send_dbmail section in books online, all I’ve done is made it dynamically accept a table instead of you needing to hard code it.

The stored procedure is very basic and I’ll probably expand it considerably for a production environment (Monday Darren! - honest), you pass in the source database, schema and table or view name of the object you want to convert into a HTML table in your email.

Usage is simple; if you have a complex query just run it into a temporary table and pass that into the procedure.

Example

select type, cnt = count(*)

into #toemail

from sys.objects

group by type

 

exec email_object_as_html @source_db    = 'tempdb',

                          @schema       = '',

                          @object_name  = '#toemail',

                          @order_clause = 'cnt desc, type',

                          @email = 'tonyrogerson@torver.net'

 

drop table #toemail

Sends an email as follows:-

Below is the table tempdb..#toemail converted from a query into HTML for your pleasure

type cnt
S 41
U 16
IT 3
SQ 3
AF 1
P 1
PC 1

Anyway have a play, usual provisos because I’m using dynamic SQL, I’ve not put the injection protection in this because I wanted to keep the proc in its simplest form for the blog. HTML HT

create proc email_object_as_html

    @source_db    sysname,       --  Where the @object_name is resident

    @schema       sysname,       --  Schema name eg.. dbo.

    @object_name  sysname,       --  Table or view to email

    @order_clause nvarchar(max), --  The order by clause eg. x, y, z

    @email        nvarchar(max)  --  Email recipient list

as

begin

    /**

        Convert's the specified table or view into an html table and emails it.

 

        Tony Rogerson, SQL Server MVP

        28 March 2008

        http://sqlblogcasts.com/blogs/tonyrogerson

 

        You are free to use and modify this,

        just keep the above in place;

        I offer no warranties, you get this as is.

 

    **/

 

    declare @subject nvarchar(max),

               @body    nvarchar(max)

 

    --  Get columns for table headers..

    exec( '

    declare col_cur cursor for

        select name

        from ' + @source_db + '.sys.columns

        where object_id = object_id( ''' + @source_db + '.' + @schema + '.' + @object_name + ''')

        order by column_id

        ' )

 

    open col_cur

 

    declare @col_name sysname

    declare @col_list nvarchar(max)

 

    fetch next from col_cur into @col_name

 

    set @body = N'<table border=1 cellpadding=1 cellspacing=1><tr>'

 

    while @@fetch_status = 0

    begin

        set @body = cast( @body as nvarchar(max) )

                  + N'<th>' + @col_name + '</th>'

 

        set @col_list = coalesce( @col_list + ',', '' ) + ' td = ' + cast( @col_name as nvarchar(max) ) + ', '''''

 

        fetch next from col_cur into @col_name

 

    end

 

    deallocate col_cur

 

    set @body = cast( @body as nvarchar(max) )

              + '</tr>'

 

    declare @query_result nvarchar(max)

    declare @nsql nvarchar(max)

 

    --  Form the query, use XML PATH to get the HTML

    set @nsql = '

        select @qr =

               cast( ( select ' + cast( @col_list as nvarchar(max) )+ '

                       from ' + @source_db + '.' + @schema + '.' + @object_name + '

                       order by ' + @order_clause + '

                       for xml path( ''tr'' ), type

                       ) as nvarchar(max) )'

 

    exec sp_executesql @nsql, N'@qr nvarchar(max) output', @query_result output

 

    set @body = cast( @body as nvarchar(max) )

              + @query_result

 

    --  Send notification

    set @subject = 'Your table as requested'

 

    set @body = @body + cast( '</table>' as nvarchar(max) )

 

    set @body = '<p>Below is the table ' + @source_db + '.' + @schema + '.' + @object_name

              + ' converted from a query into HTML for your pleasure</p>'

              + cast( @body as nvarchar(max) )

 

    EXEC msdb.dbo.sp_send_dbmail  @profile_name = 'TORVER',

                                  @recipients = @email,

                                  @body = @body,

                                  @body_format = 'HTML',

                                  @subject = @subject

 

end

Filed under:

Comments

# Interesting Finds: March 29, 2008

29 March 2008 15:33 by Jason Haley

# Interesting Finds: March 29, 2008

02 April 2008 02:37 by Jason Haley

# Format query output into an HTML table - the easy way

24 October 2008 09:20 by Tony Rogerson's ramblings on SQL Server

Ok, I wrote a big stored procedure last time my entry on &quot; send table or view as embedded html &quot;;

# sp_send_dbmail with attachment issue | keyongtech

Pingback from  sp_send_dbmail with attachment issue | keyongtech

# Mailing myself an XML table. | keyongtech

Pingback from  Mailing myself an XML table. | keyongtech

# re: Send Table or View as embedded HTML <table> in an email – stored procedure

15 December 2010 19:06 by jj00

Just wanted to let you know someone is still finding this post useful.  

I used part of this code with tsqlunit to create more detailed unit test failure messages.  I didn't need the email part, so I just made a few minor modifications for my use.

Thanks!