Convert dynamically SQL Server 2008 Table to HTML table

Question

Is there a way I can convert a SQL Server 2008 Table to HTML table text, without knowing the structure of the table first?

I tried this:

USE [Altiris]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spCustomTable2HTML] (
@TABLENAME  NVARCHAR(500),
@OUTPUT   NVARCHAR(MAX) OUTPUT,
@TBL_STYLE NVARCHAR(1024) = '',
@HDR_STYLE NVARCHAR(1024) = '')
AS
-- @exec_str stores the dynamic SQL Query
-- @ParmDefinition stores the parameter definition for the dynamic SQL
DECLARE @exec_str  NVARCHAR(MAX)
DECLARE @ParmDefinition NVARCHAR(500)


--We need to use Dynamic SQL at this point so we can expand the input table name parameter
SET @exec_str= N'
DECLARE @exec_str  NVARCHAR(MAX)
DECLARE @ParmDefinition NVARCHAR(500)

--Make a copy of the original table adding an indexing columnWe need to add an index column to the table to facilitate sorting so we can maintain the
--original table order as we iterate through adding HTML tags to the table fields.
--New column called CustColHTML_ID (unlikely to be used by someone else!)
--

select CustColHTML_ID=0,* INTO #CustomTable2HTML FROM ' + @TABLENAME + ' 

--Now alter the table to add the auto-incrementing index. This will facilitate row finding
DECLARE @COUNTER INT
SET @COUNTER=0
UPDATE #CustomTable2HTML SET @COUNTER = CustColHTML_ID=@COUNTER+1 

-- @HTMLROWS will store all the rows in HTML format
-- @ROW will store each HTML row as fields on each row are iterated through 
-- using dymamic SQL and a cursor
-- @FIELDS will store the header row for the HTML Table

DECLARE @HTMLROWS NVARCHAR(MAX) DECLARE @FIELDS NVARCHAR(MAX) 
SET @HTMLROWS='''' DECLARE @ROW NVARCHAR(MAX) 

-- Create the first HTML row for the table (the table header). Ignore our indexing column!
SET @FIELDS=''<tr ' + @HDR_STYLE + '>''
SELECT @FIELDS=COALESCE(@FIELDS, '' '','''')+''<td>'' + name + ''</td>''
FROM tempdb.sys.Columns
WHERE object_id=object_id(''tempdb..#CustomTable2HTML'')
AND name not like ''CustColHTML_ID''
SET @FIELDS=@FIELDS + ''</tr>''

-- @ColumnName stores the column name as found by the table cursor
-- @maxrows is a count of the rows in the table, and @rownum is for marking the
-- ''current'' row whilst processing

DECLARE @ColumnName  NVARCHAR(500)
DECLARE @maxrows INT
DECLARE @rownum INT

--Find row count of our temporary table
SELECT @maxrows=count(*) FROM  #CustomTable2HTML


--Create a cursor which will look through all the column names specified in the temporary table
--but exclude the index column we added (CustColHTML_ID)
DECLARE col CURSOR FOR
SELECT name FROM tempdb.sys.Columns
WHERE object_id=object_id(''tempdb..#CustomTable2HTML'')
AND name not like ''CustColHTML_ID''
ORDER BY column_id ASC

--For each row, generate dymanic SQL which requests the each column name in turn by 
--iterating through a cursor
SET @rowNum=0
SET @ParmDefinition=N''@ROWOUT NVARCHAR(MAX) OUTPUT,@rowNum_IN INT''

While @rowNum < @maxrows
BEGIN
  SET @HTMLROWS=@HTMLROWS + ''<tr>''

  SET @rowNum=@rowNum +1
  OPEN col
  FETCH NEXT FROM col INTO @ColumnName
  WHILE @@FETCH_STATUS=0
    BEGIN
      --Get nth row from table
      --SET @exec_str=''SELECT @ROWOUT=(select top 1 ['' + @ColumnName + ''] from (select top '' + cast(@rownum as varchar) + '' * from #CustomTable2HTML order by CustColHTML_ID ASC) xxx order by CustColHTML_ID DESC)''
      SET @exec_str=''SELECT @ROWOUT=(select ['' + @ColumnName + ''] from #CustomTable2HTML where CustColHTML_ID=@rowNum_IN)''

      EXEC  sp_executesql 
            @exec_str,
            @ParmDefinition,
            @ROWOUT=@ROW OUTPUT,
            @rowNum_IN=@rownum

      SET @HTMLROWS =@HTMLROWS +  ''<td>'' + @ROW + ''</td>''
      FETCH NEXT FROM col INTO @ColumnName
    END
  CLOSE col
  SET @HTMLROWS=@HTMLROWS + ''</tr>''
END

SET @OUTPUT=''''
IF @maxrows>0
SET @OUTPUT= ''<table ' + @TBL_STYLE + '>'' + @FIELDS + @HTMLROWS + ''</table>''

DEALLOCATE col
'

DECLARE @ParamDefinition nvarchar(max)
SET @ParamDefinition=N'@OUTPUT NVARCHAR(MAX) OUTPUT'

--Execute Dynamic SQL. HTML table is stored in @OUTPUT which is passed back up (as it's
--a parameter to this SP)
EXEC sp_executesql @exec_str,
@ParamDefinition,
@OUTPUT=@OUTPUT OUTPUT

RETURN 1

but when I execute the procedure

DECLARE @HTML NVARCHAR(MAX)

EXEC SpCustomTable2HTML 'Users', @HTML OUTPUT

SELECT @HTML

it keeps returning null.

Any ideas?

Take a look at virtualobjectives.com.au/sqlserver/saving_to_html.htm — kevchadders
– kevchadders, Commented Apr 26, 2013 at 9:21

RichardTheKiwi · Accepted Answer · 2013-04-26 09:29:57Z

2

This SQL Fiddle DEMO shows your problem. When ALL the columns in ALL rows have values, you get a proper HTML table. When ANY NULLs exist, it turns the entire thing into NULL because

NULL + <any> = NULL

To fix it, simply change line 90 to handle nulls, i.e.

SET @HTMLROWS =@HTMLROWS + '''' + ISNULL(@ROW,'''') + ''''

The fixed SQL Fiddle DEMO

answered Apr 26, 2013 at 9:29

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jez Walters · Accepted Answer · 2015-06-25 09:16:24Z

I realise it's been a while (to say the least) since this question was active but I thought I would post a few comments on this thread, as it turned up in a recent search.

Apologies if this (unintentionally) annoys the question asker but I believe the approach being used is both inefficient and difficult to understand - and therefore maintain.

There's no need to copy the database data before using it to generate the HTML table. It's just my humble opinion, but using dynamic SQL to generate dynamic SQL is also counter-intuitive.

Furthermore, database data values that contain HTML tags (or, worse still, malformed HTML tags) need to be escaped, so that they can be rendered correctly. For example, database data such as "value > 10" needs to generate the HTML "<td>value > 10</td>".

The following code addresses all of the above points, by using the built-in FOR XML clause:

CREATE PROCEDURE dbo.spCustomTable2HTML
    @TABLENAME nvarchar(500),
    @TBL_STYLE nvarchar(1024) = '',
    @HDR_STYLE nvarchar(1024) = '',
    @OUTPUT nvarchar(MAX) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    -- Declare variables
    DECLARE @Columns nvarchar(MAX) = '',
            @Data nvarchar(MAX),
            @SQL nvarchar(MAX);

    -- Snapshot columns (to force use of tempdb)
    IF OBJECT_ID('tempdb.dbo.##spCustomTable2HTMLColumns') IS NOT NULL
    BEGIN
        DROP TABLE ##spCustomTable2HTMLColumns;
    END

    SET @SQL = 
   'SELECT TOP 0 *
    INTO ##spCustomTable2HTMLColumns
    FROM ' + @TABLENAME;

    EXEC sp_executesql @SQL;

    -- Build header row
    SET @OUTPUT = (SELECT name AS td
                   FROM tempdb.sys.columns
                   WHERE object_id = OBJECT_ID('tempdb.dbo.##spCustomTable2HTMLColumns')
                   ORDER BY column_id
                   FOR XML RAW(''), ELEMENTS);
    SET @OUTPUT += '</tr>'

    -- Build column list
    SELECT @Columns += '[' + name + '] AS td,'
    FROM tempdb.sys.columns
    WHERE object_id = OBJECT_ID('tempdb.dbo.##spCustomTable2HTMLColumns')
    ORDER BY column_id;

    SET @Columns = LEFT(@Columns, LEN(@Columns) - 1);  -- Strip trailing comma

    -- Delete columns snapshot
    DROP TABLE ##spCustomTable2HTMLColumns;

    -- Build data rows
    SET @SQL =
   'SET @Data = CONVERT(varchar(MAX),
                        (SELECT ' + @Columns +
                       ' FROM ' + @TABLENAME +
                       ' FOR XML RAW (''tr''), ELEMENTS XSINIL))';

    EXEC sp_executesql @SQL, N'@Data NVARCHAR(MAX) OUTPUT', @Data = @Data OUTPUT;

    SET @Data = REPLACE(@Data, ' xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"', '');  -- Remove XSI namespace
    SET @Data = REPLACE(@Data, ' xsi:nil="true"', '');                                         -- Remove XSI attributes
    SET @OUTPUT += @Data;

    -- Prefix table/row headers
    SET @OUTPUT = REPLACE(@OUTPUT, ' ', '&nbsp;');                           -- Use non-breaking spaces
    SET @OUTPUT = REPLACE(@OUTPUT, '</tr>', '</tr>' + CHAR(13) + CHAR(10));  -- Add new line per row (to improve rendering in Microsoft Outlook)
    SET @OUTPUT = '<table ' + @TBL_STYLE + '>' +
                  '<tr ' + @HDR_STYLE + '>' +
                  @OUTPUT +
                  '</table>';
END

Collectives™ on Stack Overflow

Convert dynamically SQL Server 2008 Table to HTML table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related