Output Query Results as HTML

Applicability:

SQL Server 2000: Tested**

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Credits:

Author: Unknown

Date: 7 Jun 2012

Description

This SP will take a standard TSQL query with a single results set, and transform the results into HTML which can be pasted directly into a web page (this was used to format the test results below)

The SP can be easily modified to include any HTML format tags or special characters as required.

** NOTE: For use with SQL 2000, replace VARCHAR(MAX) with VARCHAR(8000)

Code

Stored Procedure:

DROP PROCEDURE dbo.usp_GetQueryResultsAsHTML

GO

CREATE PROCEDURE dbo.usp_GetQueryResultsAsHTML( @p_SQLStmt VARCHAR(8000))

AS

DECLARE

@Columns VARCHAR(MAX),

@FinalHTMLOut VARCHAR(MAX),

@ColHeader VARCHAR(MAX),

@Final VARCHAR(MAX),

@SQLStmt VARCHAR(MAX)

-- drop temporary tables used.

IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE name = '##TEMPhtml1')

DROP TABLE ##TEMPhtml1

IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE name = '##TEMPhtml2')

DROP TABLE ##TEMPhtml2

-- prepare query

SET @SQLStmt =

'select * into ##tempHTML1 from ('

+ @p_SQLStmt

+ ') as T1'

EXECUTE (@SQLStmt)

--Prepare columns details

SELECT @Columns =

COALESCE(@Columns

+ ' + '' </td><td> '' + ', '')

+ 'convert(varchar(100),isnull('

+ column_name +','' ''))'

FROM tempdb.information_schema.columns WHERE table_name='##tempHTML1'

--Prepare column Header

SET @ColHeader = '<tr bgcolor=#EDFEDF align=center>'

SELECT @ColHeader =

@ColHeader

+ '<td><b> '

+ column_name

+ '</b></td>'

FROM tempdb.information_schema.columns WHERE table_name='##tempHTML1'

SET @ColHeader=@ColHeader + '</tr>'

--prepare final output

SET @Final =

'Select ''<tr><td>'' + '

+ @Columns

+ ' ''</td></tr> '' into ##tempHTML2 from ##tempHTML1 '

EXECUTE( @Final)

SET @FinalHTMLOut =

' <html> <body><style type="text/css" media="all"> table { margin-bottom: 2em; border-collapse: collapse } td,th {border= 1 solid #999; padding: 0.2em 0.2em; font-size: 12;} </style> <table width="100%"> '

+ @ColHeader

SELECT @FinalHTMLOut =

@FinalHTMLOut

+ [</td></tr>]

FROM ##tempHTML2

SET @FinalHTMLOut =

@FinalHTMLOut

+ ' </table></body></htmL>'

-- drop temporary tables used.

IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE name = '##TEMPhtml1')

DROP TABLE ##TEMPhtml1

IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE name = '##TEMPhtml2')

DROP TABLE ##TEMPhtml2

--return final output

SELECT @FinalHTMLOut AS HTMLoutput

Test Code:

EXEC dbo.usp_GetQueryResultsAsHTML 'SELECT TOP 11 name, id, xtype, uid, info, status FROM sysobjects '

Test Results: