Stored procedure to generate HTML tables for SQL Server query output

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:

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

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:

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.

Eli Leiba

Eli Leiba is a senior application DBA at Israel Electric Company, a teacher at Microsoft CTEC, and a senior database consultant. With 19 years of experience working with both SQL Server and Oracle RDBMS. He is certified in Oracle and SQL Server database administration and implementation and has a B.S. in Computer Science. He can be reached at: iecdba@hotmail.com.

• MSSQLTips Awards: Rising Star (50+ tips) – 2019 | Author of the Year Contender – 2016-2019