Cool Tips‎ > ‎Data Formatting‎ > ‎

Data Formatting - 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:

 

name id xtype uid info status
sysrscols 3 S 4 0 0
sysrowsets 5 S 4 0 0
sysallocunits 7 S 4 0 0
sysfiles1 8 S 4 0 0
syspriorities 17 S 4 0 0
sysdbfrag 18 S 4 0 0
sysfgfrag 19 S 4 0 0
syspru 21 S 4 0 0
sysbrickfiles 22 S 4 0 0
sysphfg 23 S 4 0 0
sysprufiles 24 S 4 0 0
 

ċ
usp_GetQueryResultsAsHTML.sql
(2k)
Andy Hughes,
Jun 8, 2012, 6:33 AM
Comments