Gerrit Mantel
Programs SQL_STORED_PROCEDURE - prc_table2html

Main program

Nameprc_table2html
Type_descSQL_STORED_PROCEDURE
CommentSQLServer Stored Procedure

PURPOSE
Generate HTML table for a given table or view.

This procedure uses the SELECT FOR XML PATH method.
The following fieldtypes cannot not be processed and are shown as <Binary data>: image, sql_variant, hierarchyid/geometry/geography, varbinary, binary, timestamp, xml
This procedure returns NULL if no rows are selected.
The local variables @tag1, @tag2, @tag3 and @tag4 contain strings that are reserved words in VARCHAR or NVARCHAR tabledata.
Table- and fieldname parameter values may optionally be enclosed by [].

PARAMETERS
1 @table_name NVARCHAR(255) Table- or viewname. Temp tables are allowed.
2 @whereclause NVARCHAR(255) Optional string containing the WHERE clause to filter rows. Must begin with 'WHERE '.
3 @orderfields NVARCHAR(255) Optional comma separated list of fieldnames to order rows (ascending). For descending sortorder add ' DESC' to fieldnames.
4 @skipfields NVARCHAR(255) Optional comma separated list of fieldnames to skip.
5 @redfields NVARCHAR(255) Optional comma separated list of fieldnames to make cell data font color red.
6 @boldfields NVARCHAR(255) Optional comma separated list of fieldnames to make cell data font bold.
7 @italicfields NVARCHAR(255) Optional comma separated list of fieldnames to make cell data font italic.
8 @urlfields NVARCHAR(255) Optional comma separated list of fieldnames to make cell data a link. Urlfields must be of type VARCHAR or NVARCHAR/SYSNAME otherwise ignored.
9 @urlimages VARCHAR(255) Optional comma separated list of images to replace urlfields links. The number or elements in urlfields and urlimages must match. Prevails above urlstrings.
10 @urlstrings VARCHAR(255) Optional comma separated list of strings to replace urlfield links. The number or elements in urlfields and urlstrings must match.
11 @width VARCHAR(255) Optional comma separated values for tablewidth in pixels or % and (optional) columnwidth of first column when @rotate=1 in pixels or % (*).
12 @bgcolor VARCHAR(255) Optional comma separated values for table background color and (optional) color for highlighting even rows (**).
13 @sortable INT 0/1/2 Tablecolumns are not sortable (default) / sortable / sortable with arrows (***).
14 @rotate INT 0/1 Table is not rotated (default) / rotated. When rotated, both sortable and 2-nd bgcolor are disabled.
15 @numleft INT 0/1 Numeric fields are right aligned (default) / left aligned.
16 @html NVARCHAR(MAX) OUTPUT Output string containing the HTML text.

(*) If the table does not fit within the given width, the browser uses a width as needed for all columns to display.

(**) Don't use dark background colors as Black or DarkGray, but light colors. See https://en.wikipedia.org/wiki/Web_colors
Proposed color combinations for background and row-highlighting are:
- LightYellow, Wheat
- LightCyan, LightBlue
- White, WhiteSmoke
- GhostWhite, Lavender

(***) To implement sortable columns:
Download the java script table-sort.js from https://www.gerritmantel.nl/html_public/prog/scripts/table-sort.js
- A script line in the html <head> part must point to the java script,
e.g.: <script src="table-sort.js"></script>
- Use @sortable=1 to implement table-sort
Or - download rhw latest version of the java script table-sort.js from https://github.com/kyle-wannacott/table-sort-js
- Use @sortable=1 to implement table-sort
- Use @sortable=2 to implement table-sort with arrows

The HTML output text contains 2 sections: <style> and <table>.
The style has a unique class name exclusively for this table and contains the sections:
- table -> border 1px solid DarkGray, background-color: [1-st bgcolor] (only when @bgcolor has first color), border-collapse: collapse
- th/td -> padding 3px
- tr:nth-child(even) -> background-color: [2-nd bgcolor] (only when @bgcolor has second color)
The table layout can have 2 possible formats:
- not rotated -> the headerrow contains table fieldnames and has a fixed background color LightGray
- rotated -> the first column contains table fieldnames and has a fixed background color LightGray

EXAMPLES
-- The following example uses two bgcolors.
-- The first is used for table background, the second for row highlighting.
DECLARE @html NVARCHAR(MAX)
EXEC [dbo].[prc_table2html]
@table_name = 'TEST',
@bgcolor = 'LightYellow, Wheat',
@html = @html OUTPUT;
SELECT @html

-- The following example uses two urlfields.
-- If urlimages and/or urlstrings are used, they must have the same amount of elements (two).
-- If both urlimages and urlstrings are used for the same elements, urlimages prevail above urlstrings
DECLARE @html NVARCHAR(MAX)
EXEC [dbo].[prc_table2html]
@table_name = 'TEST',
@whereclause = 'WHERE Name = ''gerrit''',
@orderfields = 'Name',
@skipfields = 'Id',
@redfields = 'Member',
@urlfields = 'Url1, Url2',
@urlimages = '../images/home.png,',
@urlstrings = ',Link',
@bgcolor = 'LightCyan',
@html = @html OUTPUT;
SELECT @html

USED OBJECTS
- dbo.fun_format_table_name (UDF in local database)
- dbo.fun_uniquename (UDF in local database)
- dbo.fun_getfield (UDF in local database)
- dbo.fun_nsplit (UDF in local database) (STRING_SPLIT is avoided because the lack of ordering and trimming)
- dbo.fun_text2html (UDF in local database)
- dbo.fun_string_strip (UDF in local database)
- table-sort.js (External java script, only needed when @sortable=1 or @sortable=2. See parameter @sortable description for implementation)

HISTORY
2025-11-23 - Created procedure
2025-12-01 - Use SELECT FOR XML method
2025-12-03 - Implemented sortorder
2025-12-05 - Corrected handling temp table notations
2026-01-02 - Added tr class with name = bgcolor to non-rotated tables
2026-01-03 - Width and bgcolor parameters are now VARCHAR(255)
2026-01-15 - Added vertical-align:top to style of first column on rotated tables
2026-01-28 - Fixed bug: when rotate=1 and orderfields is not null, ordering does not work
- Fixed bug: when bgcolor is NULL and rotate=0 (or NULL), the output is NULL
- Fixed bug: when table has binary datatypes, column header fields where missing.
- Implemented style section in HTML output
- Fixed error: STRING_AGG aggregation result exceeded the limit of 8000 bytes
2026-01-29 - Parameter @urlimages and @urlstrings can have empty elements, see example 2.

TAGS
<program>
<description>Generate HTML table for a given table or view</description>
<minversion>14</minversion>
<generic>1</generic>
<author>Gerrit Mantel</author>
<created>2025-11-23</created>
<lastmodified>2026-01-29</lastmodified>
</program>
DescriptionGenerate HTML table for a given table or view
Minversion14.0
Generic1
AuthorGerrit Mantel
Created2025-11-23 00:00:00
Lastmodified2026-01-29 00:00:00
Xp_cmdshell0
Ole_automation0
Subprogram_cnt5
Sourcecode

Sub programs

NameType descDescriptionMinversionGenericAuthorCreatedLastmodifiedXp cmdshellOle automation
fun_format_tablenameSQL_SCALAR_FUNCTIONFormat tablename1Gerrit Mantel2025-12-04 00:00:002025-12-04 00:00:0000
fun_getfieldSQL_SCALAR_FUNCTIONReturn the n-th field from a given string, between a given separator pattern1Gerrit Mantel2000-01-01 00:00:002024-02-09 00:00:0000
fun_nsplitSQL_TABLE_VALUED_FUNCTIONSplit delimited n-string into record values1Gerrit Mantel2005-03-21 00:00:002024-04-09 00:00:0000
fun_text2htmlSQL_SCALAR_FUNCTIONReplace HTML/XML reserved characters in given text to HTML entity names1Gerrit Mantel2014-10-22 00:00:002025-11-26 00:00:0000
fun_uniquenameSQL_SCALAR_FUNCTIONReturn an unique name with format yyyy_mm_dd_hhmiss_nnnnnnn1Gerrit Mantel2000-01-01 00:00:002024-02-09 00:00:0000