/********************************************************************************************************************************/ /* TRANSFORM ARBITRARY QUERY TO HTML TABLE FOR OUTPUT I.E. AS HTML-FORMATTED DATABASE MAIL */ /********************************************************************************************************************************/ /* Author: Manfred Kipfelsberger, Date: 2014-12-16, License: CC-BY */ /********************************************************************************************************************************/ /* T-SQL Stuff Function: http://msdn.microsoft.com/en-us/library/ms188043.aspx */ /* T-SQL For XML: http://msdn.microsoft.com/en-us/library/ms178107.aspx - to concatenate row values */ /* Concatenating Row Values with For XML / Replace: http://stackoverflow.com/questions/1874966/concatenate-row-values-t-sql */ /* Query Metadata: http://sqlblog.com/blogs/aaron_bertrand/archive/2010/12/20/sql-server-v-next-denali-metadata-discovery.aspx */ /* Html Formatting: http://www.sqlservercentral.com/articles/T-SQL/99398/ This article kicked me off on the topic */ /* Output table as html: http://weblogs.sqlteam.com/mladenp/archive/2006/07/25/10771.aspx */ /* Some posts on html-formatting a table: http://www.sqlservercentral.com/Forums/Topic1465444-279-1.aspx */ /* Easy way to format ss table as html: http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/10/24/format-query-output-into-an-html-table-the-easy-way.aspxhttp://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/10/24/format-query-output-into-an-html-table-the-easy-way.aspx */ /* Even SS-MVPs use Cursors to process tables row by row: http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/03/28/send-table-or-view-as-embedded-html-lt-table-gt-in-an-email-stored-procedure.aspx, well in 2007, they did */ /* Another nice kickoff question on stackoverflow: http://stackoverflow.com/questions/7070053/convert-a-sql-query-result-table-to-an-html-table-for-email */ /* dyn sql with output var, see: http://www.sommarskog.se/dynamic_sql.html#sp_executesql */ /********************************************************************************************************************************/ -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -- p_ConvertAnyQueryToHtml -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -- Beliebige Query in Html konvertieren -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - USE GO IF OBJECT_ID(N'p_ConvertAnyQueryToHtml', 'P') IS NOT NULL DROP PROCEDURE p_ConvertAnyQueryToHtml GO CREATE PROCEDURE p_ConvertAnyQueryToHtml ( @Sql NVARCHAR(MAX), @Script nvarchar(MAX), @Css nvarchar(MAX) = '', @Before nvarchar(MAX), @After nvarchar(MAX) = '', @Html nvarchar(MAX) = '' OUTPUT ) AS BEGIN -- inspiration for formatting html table output, see: http://www.sqlservercentral.com/articles/T-SQL/99398/ DECLARE @HtmlTemplate NVARCHAR(MAX) = ' {{css}} {{script}} {{before}} {{hdrRows}} {{rows}}

{{after}} '; SET @HtmlTemplate = REPLACE(@HtmlTemplate, '{{script}}', @Script) SET @HtmlTemplate = REPLACE(@HtmlTemplate, '{{before}}', REPLICATE(CHAR(9),3) + REPLACE(@Before, CHAR(10), CHAR(10) + REPLICATE(CHAR(9),2))) SET @HtmlTemplate = REPLACE(@HtmlTemplate, '{{after}}', REPLICATE(CHAR(9),3) + REPLACE(@After, CHAR(10), CHAR(10) + REPLICATE(CHAR(9),2))) IF @css = '' SET @css = ' ' SET @css = @css + @cssCellAlign SET @HtmlTemplate = REPLACE(@HtmlTemplate, '{{css}}', @Css) -- generate Header fields DECLARE @hdrRows nvarchar(max) = '' SELECT @hdrRows = COALESCE(@hdrRows + '' + name + '', '') FROM sys.dm_exec_describe_first_result_set(@Sql, NULL, 0) AS f WHERE name IS NOT NULL SET @hdrRows = '' + @hdrRows + '' SET @HtmlTemplate = REPLACE(@htmlTemplate, '{{hdrRows}}', @hdrRows) -- http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string -- dynamically capture results of the query in a table variable - this allows to take standard Queries AND Stored Procedures as input DECLARE @tblExpr NVARCHAR(MAX) = '' SELECT @tblExpr = COALESCE(@tblExpr + ', [' + name + '] ' + system_type_name, '') FROM sys.dm_exec_describe_first_result_set(@Sql, NULL, 0) DECLARE @insertExpr NVARCHAR(MAX) = '' SELECT @insertExpr = COALESCE(@insertExpr + ', [' + name + ']', '') FROM sys.dm_exec_describe_first_result_set(@Sql, NULL, 0) SET @tblExpr = 'DECLARE @tbl TABLE (' + STUFF(@tblExpr, 1,2,'') + ');' + 'INSERT @tbl (' + STUFF(@insertExpr, 1, 2, '') + ')' + @sql + ';' --PRINT @tblExpr -- Convert all fields in query rows to table rows DECLARE @rowExpr NVARCHAR(MAX) = '' SELECT @rowExpr = COALESCE(@rowExpr + ''''' + REPLACE(dbo.f_htmlEncode([' + name + ']), '''''''', '' '') + '''' ', '') FROM sys.dm_exec_describe_first_result_set(@Sql, NULL, 0) AS f WHERE name IS NOT NULL SET @rowExpr = 'SELECT @html = COALESCE(@html + '''' + ' + REPLACE(@rowExpr, ' '''' ''', ' '''' + ''') + ' + '''', '''') FROM @tbl' DECLARE @dynSql nvarchar(max) = @tblExpr + @rowExpr; DECLARE @rows nvarchar(max) = '' EXEC sp_executeSql @dynSql, N'@html NVARCHAR(MAX) = '''''''' OUTPUT', @html = @rows OUTPUT SET @HtmlTemplate = REPLACE(@htmlTemplate, '{{rows}}', @rows) -- return html in output variable SET @html = @htmlTemplate; END GO -- ************************************************************************************************** -- USAGE -- ************************************************************************************************** USE GO IF OBJECT_ID(N'sampleData20141215', 'U') IS NOT NULL DROP TABLE sampleData20141215 GO CREATE TABLE sampleData20141215 (subscriber NVARCHAR(50), subId INT, country VARCHAR(3), total INT, articles INT, discussions INT, edits INT); GO INSERT sampleData20141215 (subscriber, subId, country, total, articles, discussions, edits) VALUES ('Carlos, Juan', 16146, 'ES', 610, 200, 200, 210), ('Perez, Roberto', 3319, 'ES', 120, 10, 90, 20), ('Jiménez, Gina', 14249, 'ES', 70, 12, 30, 28), ('Rodriguez, Samuel', 17017, 'ES', 400, 80, 300, 20), ('Navarro, Jacquelyn', 5558, 'ES', 210, 90, 45, 75), ('Grün, Thomas', 18039, 'DE', 430, 150, 150, 130), ('Flügel, Clemens', 15806, 'DE', 400, 200, 160, 40), ('Öner, Sev', 7043, 'DE', 260, 90, 90, 80), ('Meier, Claus', 7704, 'DE', 180, 70, 78, 32), ('Kelly, Maria', 7199, 'DE', 170, 10, 70, 90), ('Munoz, Tabitha', 9632, 'US', 230, 150, 50, 30), ('White, Nicholas', 18199, 'US', 430, 60, 320, 50), ('Thompson, James', 16828, 'US', 400, 200, 180, 20), ('Gonzales, Cameron', 5509, 'US', 130, 50, 44, 36), ('Chander, Tommy', 3421, 'US', 120, 40, 30, 50), ('Green, Jack', 15971, 'UK', 380, 154, 77, 149), ('Lopez, Maria', 14533, 'UK', 340, 120, 160, 60), ('Stewart, Kimberly', 8668, 'UK', 200, 150, 10, 40), ('Patterson, Caroline', 5985, 'UK', 140, 40, 50, 50), ('Beasley, Shaun', 1511, 'UK', 30, 10, 5, 25); DECLARE @sql NVARCHAR(100) = N'SELECT * FROM sampleData20141215'; DECLARE @before NVARCHAR(MAX) = '

Top 5 contributors

2nd Quarter 2014

These are the top contributors from US, UK, Germany and Spain

' DECLARE @after NVARCHAR(MAX) = '

Job-ID: 123

' DECLARE @html NVARCHAR(MAX); EXEC p_ConvertAnyQueryToHtml @sql, '', '', @before, @after, @html OUTPUT EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Fill in your profile name here', @recipients = 'Fill in your recipients here', @subject = 'Top 5 contributors', @body_format = 'HTML', @body = @html, @importance = 'Normal'; GO