Procedure to Output Tables in HTML Format in SQL Server

Hello everyone,

In this article, I will share information about the procedure for outputting tables in HTML format in SQL Server

In SQL Server, in some cases you may want to output the tables in HTML format.

You can easily do this by using the following procedure.

CREATE PROCEDURE [dbo].[SqlTableToHtml] (@TABLENAME NVARCHAR(500),
@OUTPUT NVARCHAR(MAX) OUTPUT,
@TBL_STYLE NVARCHAR(1024) = '',
@TD_STYLE NVARCHAR(1024) = '',
@HDR_STYLE NVARCHAR(1024) = '')
AS

	DECLARE @exec_str NVARCHAR(MAX)
	DECLARE @ParmDefinition NVARCHAR(500)

	SET @exec_str = N'
DECLARE @exec_str  NVARCHAR(MAX)
DECLARE @ParmDefinition NVARCHAR(500)

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

DECLARE @COUNTER INT
SET @COUNTER=0
UPDATE #CustomTable2HTML SET @COUNTER = CustColHTML_ID=@COUNTER+1 

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

SET @FIELDS=''<tr>''
SELECT @FIELDS=COALESCE(@FIELDS, '' '','''')+''<th ' + @HDR_STYLE + '>'' + name + ''</th>''
FROM tempdb.sys.Columns
WHERE object_id=object_id(''tempdb..#CustomTable2HTML'')
AND name not like ''CustColHTML_ID''
SET @FIELDS=@FIELDS + ''</tr>''

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

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

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

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
      SET @exec_str=''SELECT @ROWOUT=(select COALESCE(['' + @ColumnName + ''], '''''''') AS ['' + @ColumnName + ''] from #CustomTable2HTML where CustColHTML_ID=@rowNum_IN)''

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

      SET @HTMLROWS =@HTMLROWS +  ''<td ' + @TD_STYLE + '>'' + @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'
	EXEC sp_executesql @exec_str
					  ,@ParamDefinition
					  ,@OUTPUT = @OUTPUT OUTPUT

	RETURN 1


--Use of the Procedure


DECLARE @html NVARCHAR(MAX)
EXEC SqlTableToHtml 'Products'
					,@html OUTPUT
					,''
					,'style="border-top:1px #CCCCCC solid;padding:7px"'
					,'style="padding:7px"'
SELECT
	@html

Create and run the above procedure and you will see a result similar to the one below.

I got the result by trying the above procedure on the Products table on the Northwind database. You can also try it on your own tables.

As you can see, we have printed the table in HTML format.

Good luck to everyone in their working life and life.