Programs

SQL_STORED_PROCEDURE - prc_table2html

Main program

Name	prc_table2html
Type_desc	SQL_STORED_PROCEDURE
Comment	SQLServer Stored Procedure PURPOSE Generate an HTML table for a given table or view. This procedure uses the SELECT FOR XML PATH method. This procedure returns NULL if no rows are selected. Binary fieldtypes are displayed as '<Binary data>' in Gray text color. Fields VARCHAR and NVARCHAR cannot contain @@@n (n=1-6) as text. These words are reserved words for proper functioning of this procedure. 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 @urltargets VARCHAR(255) Optional comma separated list of url targets. The number or elements in urlfields and urltargets must match (*1). 12 @width VARCHAR(255) Optional comma separated values for tablewidth in pixels or % and (optional) columnwidth of the first column when @rotate=1 in pixels or % (*2). 13 @bgcolor VARCHAR(255) Optional comma separated values for table background color and (optional) color for highlighting even rows (*3). 14 @sortable INT 0/1/2 Tablecolumns are not sortable (default) / sortable / sortable with arrows (*4). 15 @rotate INT 0/1 Table is not rotated (default) / rotated. When rotated, both sortable and 2-nd bgcolor are disabled. 16 @numleft INT 0/1 Numeric fields are right aligned (default) / left aligned. 17 @activelink INT 0/1 Class "link" is inserted for urlfields / not inserted (default) (*5) 18 @html NVARCHAR(MAX) OUTPUT Output string containing the HTML text. (*1) Url targets can be _blank, _self, _parent, _top or a framename. If omitted, no target is used. (*2) If the table does not fit within the given width, the browser uses a width as needed for all columns to display. If all columns do not fit on the screen, the browser may ignore the 2-nd element of @width for the first column in a rotated table. (*3) For background colors, don't use dark colors such 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 (*4) 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 the 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 (*5) Use @activelink=1 to highlight urlstrings or urlimages. Implement the highlight style in your css, like: .active-link {border: 4px solid red; box-shadow: 0 0 10px red;} This is not applicable for Url targets beginning with an underscore, such as _blank, _self, _parent and _top. HTML styling is done by a <style> element, prior to the <table> element, in this way: <style> table.@table_class_name { border:1px solid DarkGray; border-collapse:collapse; width:@width1; Line, only when the 1-st element in @width is given background-color:@bgcolor1; Line, only when the 1-st element in @bgcolor is given } .TRef_Setup tr:nth-child(even) { Section, only when 2-nd element in @bcolor is given background-color:@bgcolor2; } .@table_class_name th { Section, only when @rotate=0 border:1px solid DarkGray; padding-left:3px; padding-right:3px; text-align:left; Overrule normal browser behavior text-align:center font-weight:bold; Already normal browser behavior, added because maybe suppressed in CSS } .@table_class_name td { border:1px solid DarkGray; padding-left:3px; padding-right:3px; text-align:left; vertical-align:top; } .@table_class_name thead tr { background-color:LightGray; } .@table_class_name tr td:first-child { Section, only when @rotate=1 text-align:left; font-weight:bold; background-color:LightGray; width:@width2; Line, only when the 2-nd element in @width is given } </style> <table class="@table_class_name"> The @table_class_name is composed by 'T' + @table_name with all characters stripped, except '_a-zA-Z0-9-'. The td elements get the following in-line styling: text-align:right (when @numleft=0 (default) and fieldtype is tinyint, smallint, int, real, money, float, bit, decimal, numeric, smallmoney or bigint) color:Red; (for all @redfields) font-weight:bold; (for all @boldfields) 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: 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 2-nd element in @width is given, the first td column gets this 2-nd width. When @rotate=1 and a 2-nd element in @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 2026-07-12 - Added parameter @urltargets 2026-09-03 - Added parameter @activelink 2026-10-18 - Suppress activelink for urlfields with urltargets beginning with underscore 2026-10-19 - Changed linkscript 2026-10-26 - Moved styling to style element 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-10-26</lastmodified> </program>
Description	Generate HTML table for a given table or view
Minversion	14.0
Generic	1
Author	Gerrit Mantel
Created	2025-11-23 00:00:00
Lastmodified	2026-10-26 00:00:00
Xp_cmdshell	0
Ole_automation	0
Subprogram_cnt	4
Sourcecode

Sub programs