Stored procedure to generate HTML tables for SQL Server query output

By:   |   Comments (9)   |   Related: More > Scripts


Problem

The requirement is to create an easy way to generate an HTML table as output for a SELECT query.  With this tool you can create quick and simple HTML reports.

Solution

The solution involves creating a stored procedure in the application database (dbo.usp_ConvertQuery2HTMLTable) that will take a SELECT query and transform the output into an HTML table.

The procedure steps:

  • The procedure gets one parameter, which is the SELECT query to execute.
  • The SELECT statement should use the real column names and can have a WHERE clause to limit the records returned.
  • The procedure calls the sys.dm_exec_describe_first_result_set dynamic view to get the column names and concatenate the columns into a string separated by commas.
  • The procedure adds the ISNULL function to make sure TD tags are generated for NULL column values.
  • The procedure then adds a FOR XML clause to the original SELECT statement and also adds TR tags for each row and another TABLE tag as the root tag.  This transforms the original query output to HTML table format.

SQL Server Stored Procedure to Generate HTML Output

For this example, I am creating this in the Northwind database.

CREATE PROC dbo.usp_ConvertQuery2HTMLTable (@SQLQuery NVARCHAR(3000))
AS
BEGIN
   DECLARE @columnslist NVARCHAR (1000) = ''
   DECLARE @restOfQuery NVARCHAR (2000) = ''
   DECLARE @DynTSQL NVARCHAR (3000)
   DECLARE @FROMPOS INT

   SET NOCOUNT ON

   SELECT @columnslist += 'ISNULL (' + NAME + ',' + '''' + ' ' + '''' + ')' + ','
   FROM sys.dm_exec_describe_first_result_set(@SQLQuery, NULL, 0)

   SET @columnslist = left (@columnslist, Len (@columnslist) - 1)
   SET @FROMPOS = CHARINDEX ('FROM', @SQLQuery, 1)
   SET @restOfQuery = SUBSTRING(@SQLQuery, @FROMPOS, LEN(@SQLQuery) - @FROMPOS + 1)
   SET @columnslist = Replace (@columnslist, '),', ') as TD,')
   SET @columnslist += ' as TD'
   SET @DynTSQL = CONCAT (
         'SELECT (SELECT '
         , @columnslist
         ,' '
         , @restOfQuery
         ,' FOR XML RAW (''TR''), ELEMENTS, TYPE) AS ''TBODY'''
         ,' FOR XML PATH (''''), ROOT (''TABLE'')'
         )

   EXEC (@DynTSQL)
   SET NOCOUNT OFF
END
GO

Create sample table and data.

CREATE TABLE [dbo].[Products]
   (
   Product [varchar](50) NULL,
   UnitPrice [money] NULL
   )
GO 

INSERT INTO [dbo].[Products]
SELECT 'Nougat Creme', 14.00
UNION
SELECT 'Gorgonzola', 12.00
UNION
SELECT 'Ale', 14.00
UNION
SELECT 'Chocolate', 10.00
UNION
SELECT 'Lager', 12.00
UNION
SELECT 'Bread', 9.00

Example of using the procedure.

usp_ConvertQuery2HTMLTable 'SELECT Product, UnitPrice FROM dbo.Products'

This generates data looks like this:

SQL Server Stored Procedure to Generate HTML Output query results

If we open this data in an HTML editor the data looks like this:

html table output

Send the Output to HTML File

If we want the query results to be saved to a file, we can use the BCP command to export the output of the procedure to an actual HTML file. The BCP command combined with the query looks like this: 

bcp "exec northwind.dbo.usp_ConvertQuery2HTMLTable 'SELECT Product, UnitPrice FROM dbo.Products'" queryout report.html -c -T -S PCN70152\SQL2K14

The parameters for the BCP command are as follows:

  • queryout - specifies you want the output saved to a file, which is report.html in our case
  • -c - this outputs the data a character data type
  • -T - this specifies a trusted connection
  • -S - this is the instance of SQL Server we want to connect to

The file could then be viewed and edited like any other HTML file and emailed to others as needed.

Create Border Around HTML Table

As you may have noticed, the output does not have a border, so the data runs together.  To fix this we could append a style to the output using a batch file as follows.

Here is an example of a simple style tag that creates a 1 pixel solid blue border.  This needs to be saved to a file named style.txt.

<style>
  table {
    border-collapse: collapse;
  }
  th, td {
    border: 1px solid blue;
    padding: 10px;
    text-align: left;
  }
</style>

Here is the code to run.  You can put this in a batch file like cr-html.bat to create the file.

  • The first line is the same BCP command as we have above.
  • The second line copies the contents of sytle.txt and report.html to a new file called s_report.html.
bcp "exec northwind.dbo.usp_ConvertQuery2HTMLTable 'SELECT Product, UnitPrice FROM dbo.Products'" queryout report.html -c -T -S PCN70152\SQL2K14
Copy /Y style.txt + report.html s_report.html

Here is what the output looks like:

html table output with borders
Next Steps
  • You can create this procedure in your application database and use it as a tool for reporting selected queries in HTML format whenever needed.
  • You can compile and create the procedure in the master database and use it for all your user databases.
  • The procedure was created and tested with: SQL Server 2014 and 2016 Standard Edition
  • Note that sys.dm_exec_describe_first_result_set dynamic is available with SQL Server 2012 and later.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Eli Leiba Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Saturday, July 22, 2023 - 12:45:01 PM - Lince Sebastian Back To Top (91422)
I wrote a script that will use PowerShell to extract TSQL/SP results and deliver them over email, eliminating the requirement to map columns to HTML code in TSQL.

https://www.dbascrolls.com/2023/07/Any-TSQLResults2HTML-EMail.html

Friday, July 7, 2023 - 9:21:17 AM - Tim Cartwright Back To Top (91367)
I did not notice Terrys submission until just now so I developed one that also included headers. I also cleaned up the code a bit.

https://gist.github.com/tcartwright/a38ecd0f8f8967c0eac5d3e691d4290b

Wednesday, June 30, 2021 - 2:29:34 AM - Roshan Bajiya Back To Top (88920)
Thank you for the explanation!! It would be great if you please share this code in MySql. Urgently needed

Wednesday, July 15, 2020 - 3:08:59 PM - Terry Losansky Back To Top (86139)
--Here is a modified version with headers. Also included is useage exporting results to a table and then a variable.

CREATE PROCEDURE dbo.usp_ConvertQuery2HTMLTable
 @SQLQuery NVARCHAR(3000) -- the SQL query text to execute. i.e. 'SELECT Column1 FROM dbo.view'
AS
/* =============================================
returns the @SQLQuery as an HTML table with header
============================================= */
BEGIN
 SET NOCOUNT ON  DECLARE @headerlist NVARCHAR (1000) = ''
 DECLARE @columnslist NVARCHAR (1000) = ''
 DECLARE @restOfQuery NVARCHAR (2000) = ''
 DECLARE @DynTSQL NVARCHAR (3000)
 DECLARE @FROMPOS INT  SELECT -- quote the header names
  @headerlist += 'ISNULL (''' + NAME + ''',' + '''' + ' ' + '''' + ')' + ','
 FROM
  sys.dm_exec_describe_first_result_set(@SQLQuery, NULL, 0)
 SELECT -- bracket NAME if input arg has [colum name] with spaces
  @columnslist += 'ISNULL ([' + NAME + '],' + '''' + ' ' + '''' + ')' + ','
 FROM
  sys.dm_exec_describe_first_result_set(@SQLQuery, NULL, 0)  SET @headerlist = LEFT(@headerlist, LEN (@headerlist) - 1) -- trim the last comma from the list
 SET @columnslist = LEFT(@columnslist, LEN (@columnslist) - 1) -- trim the last comma from the list
 SET @FROMPOS = CHARINDEX ('FROM', @SQLQuery, 1)
 SET @restOfQuery = SUBSTRING(@SQLQuery, @FROMPOS, LEN(@SQLQuery) - @FROMPOS + 1)  SET @headerlist = Replace (@headerlist, '),', ') as th,')
 SET @headerlist += ' as th'  SET @columnslist = Replace (@columnslist, '),', ') as td,')
 SET @columnslist += ' as td'
 SET @DynTSQL = CONCAT (
 'SELECT CONVERT(NVARCHAR(MAX), (SELECT  
  (SELECT '
   ,@headerlist
   , ' '
   ,' FOR XML RAW(''tr''), ELEMENTS, TYPE) AS ''thead'','
   ,' ( SELECT '
   ,  @columnslist
     ,' '
     ,@restOfQuery
    ,' FOR XML RAW (''tr''), ELEMENTS, TYPE) AS ''tbody''',
    ' FOR XML PATH (''''), ROOT (''table'')))'
   )  EXECUTE (@DynTSQL)
END
GO -- Sample Useage -- get the results into a table variable
DECLARE @t TABLE (resultSet xml)
INSERT @t (resultSet)
EXECUTE dbo.usp_ConvertQuery2HTMLTable N'SELECT Product, UnitPrice FROM dbo.Products' SELECT resultSet FROM @t -- get the result into a variable
DECLARE @result xml SELECT TOP 1 @result = resultSet FROM @t SELECT @result

Monday, August 13, 2018 - 10:22:34 AM - Stu Henning Back To Top (77161)

Thank you for the explanation. It would serve my purpose exactly but I don't know how to pass the string that is the html table to a stored procedure that has called this one.


Thursday, June 21, 2018 - 3:17:26 AM - Keerthivasan Back To Top (76266)

 

  So if i want to include the header inside the table how do i do it..?


Saturday, May 5, 2018 - 6:31:57 AM - Ajit Kumar Maharana Back To Top (75869)

 

  CONCAT (

         'SELECT (SELECT '
         , @columnslist
         ,' '
         , @restOfQuery
         ,' FOR XML RAW (''TR''), ELEMENTS, TYPE) AS ''TBODY'''
         ,' FOR XML PATH (''''), ROOT (''TABLE'')'
         )

HI, Thanks for the code. This is helpful, but CONCAT function is not avialable on SSMS 2008. So the function throughing error. Could you please suggest how could I get this fix on SSMS 2008 R2

Thursday, April 26, 2018 - 6:49:05 PM - JW Back To Top (75794)

 

This is nice, but it would be cooler if we got the column names as outputs in the table. Either by default or optionally...


Friday, April 6, 2018 - 11:40:52 AM - Gabriel Back To Top (75628)

 Hi,

 

 

Great procedure; thanks a lot; but how can I insert in html file  table header?

 

Thanks a lot!

 















get free sql tips
agree to terms