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.
Binary fieldtypes cannot not be processed.
This procedure returns NULL if no rows are selected.
The values assigned to local variables @tag1, @tag2, @tag3 and @tag4 are reserved words in VARCHAR or NVARCHAR tabledata and should be avoided.
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 table 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 styling code is mostly inline coded for the tags
table border-collapse:collapse;[ background-color:@bgcolor1;][ width="@width1"]
th border:1px solid DarkGray; padding-left:3px; padding-right:3px; text-align:left;
tr (in thead) background-color:LightGray
td border:1px solid DarkGray; padding-left:3px; padding-right:3px; vertical-align:top; text-align:left/right;

The HTML styling for highlighting even rows is done by adding a style element before table.
This can be activated by giving a 2nd color in @bgcolor.
<style>
table.@bgcolor2 tr:nth-child(even) {background-color:@bgcolor2;}
</style>

The td text alignment is normally left. However, the fieldtypes tinyint, smallint, int, real, money,
float, bit, decimal, numeric, smallmoney, bigint are right aligned, unless @numleft is set to 0.

Extra td styling code is added when these parameters are used:
td color:Red; (for all @redfields)
td font-weight:bold; (for all @boldfields)
td font-style:italic; (for all @italicfields)

Binary fieldtypes (image, sql_variant, hierarchyid/geometry/geography, varbinary, binary, timestamp or xml)
are displayed as '<Binary data>' and a style code is added:
td color:Gray

When @rotate=1 then the table is rotated and the first td column contains table fieldnames and has a fixed background color LightGray.
When @rotate=1 and a second @width is given, the first td column gets: width="@width2"
When @rotate=1 and a second @bgcolor is given, this is ignored: row higlighting in rotated tables is not possible.
When @rotate=1 and @sortable=1, then sortable is ignored.

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_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)
- 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.
2026-02-07 - Added vertical-align:top to td
2026-06-07 - All makeup is done by inline styles, except for row-highlighting
- Removed TYPE from SELECT FOR XML statement to prevent getting selfclosing td tags

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-06-07</lastmodified>
</program>
DescriptionGenerate HTML table for a given table or view
Minversion14.0
Generic1
AuthorGerrit Mantel
Created2025-11-23 00:00:00
Lastmodified2026-06-07 00:00:00
Xp_cmdshell0
Ole_automation0
Subprogram_cnt4
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