Post date: Dec 9, 2011 10:53:22 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 INTAS 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)), '' '') + ''</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 usageIF 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 @oidEXEC dbo.CreateHTMLTableOutput @tempTableid = @oid
Example output