Just Code‎ > ‎

Tsql - Stored procedure to generate HTML table output from any query

posted Dec 9, 2011, 2:53 AM by Peter Henell   [ updated Jan 25, 2012, 5:17 AM ]
I wanted some way of running a query and then copy paste the output as a HTML table directly into Google Sites. Now, Google Sites does not allow CSS templates so we need to do all of that inline.

I created this simple stored procedure to which you send the objectID of a temporary table containing the result that you wish to publish on your web site. The code is not pretty but it works!
ALTER PROCEDURE dbo.CreateHTMLTableOutput 
    @tempTableid INT
AS
    
    SET concat_null_yields_null OFF
    
    
    DECLARE @tablename VARCHAR(MAX)
    SELECT @tableName = SCHEMA_NAME(schema_id) + '.' + name FROM tempdb.sys.objects WHERE OBJECT_ID = @tempTableid

    DECLARE @columns TABLE(Name VARCHAR(255))
    INSERT @columns 
    SELECT name FROM tempdb.sys.columns AS c WHERE c.object_id = @tempTableid
    
    DECLARE @formatedColumns VARCHAR(MAX) = ''
    SELECT @formatedColumns = @formatedColumns + '''<td style="padding: 5px 10px;color: #666;">''+ isnull(cast('+ c.name +' as varchar(1000)), ''&nbsp;'') + ''</td>''+ '
    FROM @columns AS c
    
    SET    @formatedColumns = SUBSTRING(@formatedColumns, 0, LEN(@formatedColumns))

    DECLARE @finalData TABLE(formated VARCHAR(MAX))
    INSERT @finalData
    EXEC ('select ''<tr>''+ ' + @formatedColumns + ' + ''</tr>'' from ' + @tableName)
    

    SELECT '<table style="border: 1px solid;border-color:#CCC;border-collapse:collapse; font: small/1.5 "Tahoma", "Bitstream Vera Sans", Verdana, Helvetica, sans-serif;">
    <thead>
        <tr style="">' 
        
    UNION ALL

    SELECT 
        '<th style="background: #FFF; color: #666;padding: 5px 10px;  border-left: 1px solid #CCC;">'+cols.name+'</th>'
    FROM    
        @columns cols
    
        
    UNION ALL
    
    SELECT '        </tr>
    </thead>
    <tbody>'
    
    UNION ALL
    
    SELECT formated FROM @finalData

    UNION ALL
    
    SELECT '    </tbody>
</table>'


go


-- EXAMPLE Of usage

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL 
    DROP TABLE #tmp

SELECT * 
INTO #tmp
FROM tSQLt_Example.tSQLt.TestResult

DECLARE @oid INT = OBJECT_ID('tempdb..#tmp')
PRINT @oid
EXEC dbo.CreateHTMLTableOutput @tempTableid = @oid

Example output
Id Class TestCase Name TranName Result Msg
12 AcceleratorTests test ready for experimentation if 2 particles [AcceleratorTests].[test ready for experimentation if 2 particles] tSQLtTran8C40E9FC763D407BA50CC96 Failure Expected: <1> but was: <0>
13 AcceleratorTests test we are not ready for experimentation if there is only 1 particle [AcceleratorTests].[test we are not ready for experimentation if there is only 1 particle] tSQLtTranBC37646615474CA7841591D Success
14 AcceleratorTests test no particles are in a rectangle when there are no particles in the table [AcceleratorTests].[test no particles are in a rectangle when there are no particles in the table] tSQLtTran7F036CF155964A0F934E1FF Success
15 AcceleratorTests test a particle within the rectangle is returned [AcceleratorTests].[test a particle within the rectangle is returned] tSQLtTranA7EA985C732743E5B3FB923 Success
16 AcceleratorTests test a particle within the rectangle is returned with an Id, Point Location and Value [AcceleratorTests].[test a particle within the rectangle is returned with an Id, Point Location and Value] tSQLtTran1B0CF48ED4F84638A693CBB Success
17 AcceleratorTests test a particle is included only if it fits inside the boundaries of the rectangle [AcceleratorTests].[test a particle is included only if it fits inside the boundaries of the rectangle] tSQLtTran15FE1327F4EE4905B9AF0D1 Success
18 AcceleratorTests test email is sent if we detected a higgs-boson [AcceleratorTests].[test email is sent if we detected a higgs-boson] tSQLtTran58C94AD3DCD049BF8E381D6 Success
19 AcceleratorTests test email is not sent if we detected something other than higgs-boson [AcceleratorTests].[test email is not sent if we detected something other than higgs-boson] tSQLtTranB071246071AA4F1DA5546CB Success
20 AcceleratorTests test status message includes the number of particles [AcceleratorTests].[test status message includes the number of particles] tSQLtTran2B301BABA36A4AC8934CB3C Success
21 AcceleratorTests test foreign key violated if Particle color is not in Color table [AcceleratorTests].[test foreign key violated if Particle color is not in Color table] tSQLtTranC8F0715D81574FE4A19A9FE Success
22 AcceleratorTests test foreign key is not violated if Particle color is in Color table [AcceleratorTests].[test foreign key is not violated if Particle color is in Color table] tSQLtTranFD352B92367A4F039BA4E21 Success
Comments