Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


Stored procedure to generate HTML tables for SQL Server query output

By:   |   Read Comments (3)   |   Related Tips: More > Scripts

ALERT: Did you know 66% of DBAs say their workload is increasing! - Click here to learn more.


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.


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))
   DECLARE @columnslist NVARCHAR (1000) = ''
   DECLARE @restOfQuery NVARCHAR (2000) = ''


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

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

   EXEC (@DynTSQL)

Create sample table and data.

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

INSERT INTO [dbo].[Products]
SELECT 'Nougat Creme', 14.00
SELECT 'Gorgonzola', 12.00
SELECT 'Ale', 14.00
SELECT 'Chocolate', 10.00
SELECT 'Lager', 12.00
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.

  table {
    border-collapse: collapse;
  th, td {
    border: 1px solid blue;
    padding: 10px;
    text-align: left;

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.

Last Update:

next webcast button

next tip button

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.

View all my tips

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Saturday, May 05, 2018 - 6:31:57 AM - Ajit Kumar Maharana Back To Top



         '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


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 06, 2018 - 11:40:52 AM - Gabriel Back To Top




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


Thanks a lot!


Learn more about SQL Server tools