top of page
OFFICIUM INSERVIO
Your reliable partner for your business software...
Prozedur: Datentabelle als Html / Procedure: Datatable as Html
(alle Versionen/all versions)
Freitag, 30. Dezember 2022
Beispielverwendung/Example how to use
Beschreibung/Description
Ein möglicher Ansatz, um Daten als Html-Tabelle zu erhalten.
A possible approach to get data as Html table.
Beispielverwendung/Example how to use
DECLARE @lRC int
DECLARE @sSqlSelect nvarchar(max)
DECLARE @lOptionalTopRecord int
DECLARE @sTableHtmlId nvarchar(255)
DECLARE @sTableHeaderCaptions nvarchar(max)
DECLARE @bTableHeaderCaptionsContainHtml bit
DECLARE @nStyle smallint
DECLARE @bTableDataContainsHtml bit
DECLARE @bTableAllowSort bit
DECLARE @sReturnHtml nvarchar(max)
-- Avoid complex Sql! Use a view instead!
SET @sSqlSelect = N'SELECT Artikelnummer, Matchcode FROM dbo.KHKArtikel WITH(Readuncommitted) WHERE Mandant=123 ORDER BY Artikelnummer'
-- It is recommended to limit the output, like in paging!
-- Otherwise the stored procedure might run a long time, depending on the amount of data.
SET @lOptionalTopRecord = 50
-- If the Html table is being referred to in any other Html code, the table should be given a proper name!
-- Otherwise, use a dummy name.
SET @sTableHtmlId = N'MyTable1'
-- You must set the header captions!
SET @sTableHeaderCaptions = N'<b>Item number</b>;Item caption'
SET @bTableHeaderCaptionsContainHtml = 1
SET @nStyle = 1
SET @bTableDataContainsHtml = 0
-- Allowing to sort the result might not work in all browsers!
SET @bTableAllowSort = 1
EXECUTE @lRC = [dbo].[spSAGGetHtmlTable]
@sSqlSelect
,@lOptionalTopRecord
,@sTableHtmlId
,@sTableHeaderCaptions
,@bTableHeaderCaptionsContainHtml
,@nStyle
,@bTableDataContainsHtml
,@bTableAllowSort
,@sReturnHtml OUTPUT
SELECT @sReturnHtml
GOErgebnis/Result
Abhängigkeiten/Requirements
https://www.officium-inservio.com/ms-sql/functionremoveleftorright
https://www.officium-inservio.com/ms-sql/functiontablesplitstring
Wichtig/Important
Der in dieser Datenbankprozedur verwendete Ansatz ist bewusst sehr einfach gehalten und ausdrücklich für kleine Datenmengen konzipiert.
Mit dem primären Ziel, den Quellcode einfach und lesbar zu halten, um bei Bedarf auch bei einzelnen Ausgabezeilen in die Datenausgabe eingreifen zu können.
Es steht völlig außer Frage, dass es mit SQL (insbesondere mit dem Microsoft SQL-Server) weitaus performantere Wege gibt, um sehr umfangreiche Datenmengen in Html umzuwandeln!
Um den Quellcode lesbar zu halten, wird für einige Parsingvorgänge ein etwas "hässlicher" Workaround verwendet.
Es gibt höchstwahrscheinlich bessere Möglichkeiten, die Daten zu analysieren, aber die aktuelle Version erfüllt die Aufgabe gut, eine schnelle Datentabelle für eine begrenzte Anzahl von Datensätzen zu erhalten.
Sie sollten sich darüber im Klaren sein, dass der Ansatz dieser Datenbankprozedur nur eine sehr begrenzte Prüfung zur Vermeidung jeglicher SQL-Injection enthält!
Bitte stellen Sie sicher, dass die Eingabe-SQL gültig und in keiner Weise "schädlich" ist.
Es wird dringend empfohlen, die Quelldaten in einer SQL-Sicht zu kapseln und diese Datenbankprozedur nur mit einer sehr einfachen SQL-Select-Anweisung aufzurufen!
The approach used in this stored procedure is deliberately kept very simple and designed specifically for small amounts of data.
The primary goal is to keep the source code simple and readable in order to be able to intervene in the data output, even with each individual output line if necessary.
There is absolutely no question that with SQL (especially with the Microsoft SQL Server), there are far better-performing ways to convert very large amounts of data into Html!
To keep the source code readable, for some parsing, a somewhat "ugly" workaround is being used.
There are most likely better ways to parse the data, but the current version serves the purpose well of getting a quick data table for a limited amount of data records.
You should be aware that this procedure only contains a very limited approach to avoid any SQL injection!
Please, make sure that the input SQL is valid and not "harmful" in any way.
It is highly recommended to encapsulate the source data into an SQL view and just call this stored procedure with a very simple SQL select statement only!
T-SQL Definition
IF EXISTS ( SELECT TOP 1 [Name] FROM dbo.sysobjects WITH(Readuncommitted)
WHERE id = OBJECT_ID(N'[dbo].[spSAGGetHtmlTable]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1 )
BEGIN
DROP PROCEDURE [dbo].[spSAGGetHtmlTable]
END
GO
CREATE PROCEDURE [dbo].[spSAGGetHtmlTable]
(
@sSqlSelect nvarchar(max)
,@lOptionalTopRecord int
,@sTableHtmlId nvarchar(255)
,@sTableHeaderCaptions nvarchar(max)
,@bTableHeaderCaptionsContainHtml bit
,@nStyle smallint
,@bTableDataContainsHtml bit
,@bTableAllowSort bit
,@sReturnHtml nvarchar(max) OUT
)
AS
-- Purpose:
-- Get a simple Html table!
-- The column data will be transformed into simple strings.
-- Any complex Sql statements should be put into a Sql view!
-- Which gives you more control also how to transfer the data columns into strings.
-- Like currency or date value formatting.
-- Returns 0 if success; otherwise an error code might be returned.
-- Requirements/dependencies:
-- This procedure needs other functions, and/or procedures:
-- "gtblSAG_Get_Text_Split"
-- "gsSAG_Remove_LeftOrRight"
-- Parameters:
-- @sSqlSelect =
-- Mandatory: Simple SQL select statement. No(!) complex joins, no "update", "alter" SQL statements or the like.
-- Also no "TOP" instruction (see the separate parameter of this procedure).
-- Tip: Encapsulate more complex queries in an SQL view and then pass the view in a simple SQL select statement!
--
-- @lOptionalTopRecord =
-- Recommended: Record count for a "TOP" selection.
-- If specified (>0), then this maximum number of data records is selected.
--
-- @sTableHtmlId =
-- Recommended: Content for ID tag of table/name of table for returned html.
--
-- @sTableHeaderCaptions =
-- Mandatory: Column header captions. Use semicolon to separate the columns.
--
-- @bTableHeaderCaptionsContainHtml =
-- Mandatory: Specify if the parameter for the column header captions contain Html instructions.
-- Caution: If activated, the caller is responsible for ensuring that the column header captions are valid HTML statements!
--
-- @nStyle =
-- Mandatory: Select the CCS formatting style.
-- Currently, this procedure supports six types (1-6).
--
-- @bTableDataContainsHtml =
-- Mandatory: Specify if the parameter for the column data contain Html instructions.
-- Caution: If activated, the caller is responsible for ensuring that the column data content is valid HTML!
--
-- @bTableAllowSort =
-- Mandatory: Specify whether the table should be designed as sortable using JavaScript (not supported by all browsers!).
-- Any JavaScript is "manageable", i.e. "uncritical" for most browsers and is usually not(!) blocked.
-- Under certain circumstances, however, such scripts are ignored/blocked, e.g. in HTML-formatted e-mail content by certain e-mail programs!
-- Common HTML scripting standards are adhered to.
--
-- @sReturnHtml =
-- Mandatory: Ouput of the fully generated HTML for the table, including the data.
-- Any JavaScript is "manageable", i.e. "uncritical" for most browsers and is usually not(!) blocked.
-- Under certain circumstances, however, such scripts are ignored/blocked, e.g. in HTML-formatted e-mail content by certain e-mail programs!
-- Common HTML scripting standards are adhered to.
-- To keep the source code readable, for some parsing, a somewhat "ugly" workaround is being used.
-- There are most likely better ways to parse the data, but the current version serves the purpose well of getting a quick data table for a limited(!) amount of data records.
-- You should be aware that this procedure only contains a very limited approach to avoid any SQL injection!
-- Please, make sure that the input SQL is valid and not "harmful" in any way.
-- Again, it is highly recommended to encapsulate the source data into an SQL view and just call this stored procedure with a very simple SQL select statement only!
SET NOCOUNT ON
DECLARE @sProcedure NVARCHAR(255)
SET @sProcedure = 'spSAGGetHtmlTable'
SET @sReturnHtml = N'<html>SAG: 202111171: ' + @sProcedure + N': No valid data!</html>'
SET @bTableHeaderCaptionsContainHtml = ISNULL(@bTableHeaderCaptionsContainHtml, 0)
SET @lOptionalTopRecord = ISNULL(@lOptionalTopRecord, 0)
SET @nStyle = ISNULL(@nStyle, 0)
IF @nStyle = 0 SET @nStyle =1
SET @bTableDataContainsHtml = ISNULL(@bTableDataContainsHtml, 0)
SET @bTableAllowSort = ISNULL(@bTableAllowSort, 0)
SET @sTableHtmlId = LTRIM(RTRIM(ISNULL(@sTableHtmlId, N'')))
IF @sTableHtmlId = N''
BEGIN
SET @sTableHtmlId = REPLACE(CAST(NEWID() AS nvarchar(255)), N'-', N'')
END
SET @sTableHtmlId = ( SELECT TOP 1 @sTableHtmlId FOR XML PATH('') )
SET @sSqlSelect = LTRIM(RTRIM(ISNULL(@sSqlSelect, N'')))
-- Check table header columns
DECLARE @sErrorTableHeaderCaptions NVARCHAR(MAX)
SET @sErrorTableHeaderCaptions = N'No table header captions provided! You must provide a <b>semicolon</b> separated list of table header captions! '
+ N'The amount of header captions must match the selected field column count!</html>'
SET @sTableHeaderCaptions = LTRIM(RTRIM(ISNULL(@sTableHeaderCaptions, N'')))
IF @sTableHeaderCaptions = N''
BEGIN
SET @sReturnHtml = N'<html>SAG: 202111174: ' + @sProcedure + N': ' + @sErrorTableHeaderCaptions
RETURN 1
END
-- Check Sql statement
IF @sSqlSelect = N''
BEGIN
SET @sReturnHtml = N'<html>SAG: 202111176: ' + @sProcedure + N': No Sql select clause provided!</html>'
RETURN 3
END
DECLARE @sErrorSqlSelectStatement NVARCHAR(MAX)
SET @sErrorSqlSelectStatement = N'Invalid or too complex Sql select clause provided. Premises: Sql must be a <b>most simple</b> Sql select statement.<br/>'
+ N'It <b>must</b> contain <b>one</b> "<c>Select</c>" and "<c>Where</c>" clause.<br/>'
+ N'It must <b>not</b> contain a <c>TOP</c> clause!</i><br/>'
+ N'It must <b>not</b> contain <c>*</c> for column selection! I.e. provide all column names <i>separately!</i><br/>'
+ N'<b>No semicolon</b> allowed!<br/>'
+ N'<b>No altering Sql keywords</b> allowed!<br/>'
+ N'Hint: consider putting complex Sql into an Sql view and provide this method with the name of the Sql view with a very simple Sql-Clause instead.<br/>'
+ N'Also, consider converting all data columns of your view into <b>string</b> values. By doing so, you can control yourself how currency or data values are to be formatted.<br/>'
+ N'Be aware to set the parameters for the table generation properly, regarding whether the actual data content contains valid Html code or not!<br/>'
+ N'</html>'
IF CHARINDEX(N';', @sSqlSelect) > 0
OR LEFT(UPPER(@sSqlSelect), LEN(N'DROP ')) = N'DROP '
OR CHARINDEX(N' DROP ', UPPER(@sSqlSelect)) > 0
OR LEFT(UPPER(@sSqlSelect), LEN(N'DELETE ')) = N'DELETE '
OR CHARINDEX(N' DELETE ', UPPER(@sSqlSelect)) > 0
OR LEFT(UPPER(@sSqlSelect), LEN(N'INSERT ')) = N'INSERT '
OR CHARINDEX(N' INSERT ', UPPER(@sSqlSelect)) > 0
OR LEFT(UPPER(@sSqlSelect), LEN(N'UPDATE ')) = N'UPDATE '
OR CHARINDEX(N' UPDATE ', UPPER(@sSqlSelect)) > 0
OR LEFT(UPPER(@sSqlSelect), LEN(N'ALTER ')) = N'ALTER '
OR CHARINDEX(N' ALTER ', UPPER(@sSqlSelect)) > 0
OR CHARINDEX(N' TOP ', UPPER(@sSqlSelect)) > 0
-- Must contain Select and Where
OR CHARINDEX(N' SELECT ', UPPER(N' ' + @sSqlSelect)) = 0
OR CHARINDEX(N' WHERE ', UPPER(N' ' + @sSqlSelect)) = 0
BEGIN
SET @sReturnHtml = N'<html>SAG: 202111177: ' + @sProcedure + N': ' + @sErrorSqlSelectStatement
RETURN 4
END
DECLARE @sSqlOnlyFromClause NVARCHAR(MAX)
DECLARE @sSqlOnlyFields NVARCHAR(MAX)
SET @sSqlOnlyFromClause = [dbo].[gsSAG_Remove_LeftOrRight] ( @sSqlSelect , N'SELECT' , 1 , 0 )
SET @sSqlOnlyFields = SUBSTRING( @sSqlOnlyFromClause , 1 , CHARINDEX( N'FROM ', @sSqlOnlyFromClause ) - 1 )
SET @sSqlOnlyFromClause = SUBSTRING( @sSqlOnlyFromClause , CHARINDEX( N'FROM ', @sSqlOnlyFromClause ) , LEN(@sSqlOnlyFromClause) )
IF CHARINDEX(N'*', @sSqlOnlyFields) > 0
BEGIN
SET @sReturnHtml = N'<html>SAG: 202111178: ' + @sProcedure + N': ' + @sErrorSqlSelectStatement
RETURN 5
END
DECLARE @sHtmlCss nvarchar(max)
DECLARE @sHtmlClassId1 nvarchar(20)
DECLARE @sHtmlTableOuter nvarchar(max)
DECLARE @sHtmlTableHeaders nvarchar(max)
DECLARE @sHtmlTableHeaderCaptionTemplate nvarchar(255)
DECLARE @sHtmlTableRows nvarchar(max)
DECLARE @sHtmlTableRowTemplate nvarchar(MAX)
DECLARE @sHtmlTableRowColTemplate1 nvarchar(MAX)
DECLARE @sHtmlTableRowColTemplate2 nvarchar(MAX)
DECLARE @sHtmlSortScript nvarchar(max)
SET @sHtmlSortScript = N''
SET @sHtmlTableRowColTemplate1 = N''
SET @sHtmlTableRowColTemplate2 = N''
-- Usw free www tools for CSS , example: https://www.tablesgenerator.com/html_tables#
IF @nStyle < 0
OR @nStyle > 6
BEGIN
SET @nStyle = 1
END
SET @sHtmlClassId1 = N'tg-0lax'
IF @nStyle = 1
BEGIN
SET @sHtmlCss = N'<style type="text/css">
.tg {border-collapse:collapse;border-color:#9ABAD9;border-spacing:0;}
.tg td{background-color:#EBF5FF;border-color:#9ABAD9;border-style:solid;border-width:1px;color:#444;
font-family:Arial, sans-serif;font-size:14px;overflow:hidden;padding:10px 5px;word-break:normal;}
.tg th{background-color:#409cff;border-color:#9ABAD9;border-style:solid;border-width:1px;color:#fff;
font-family:Arial, sans-serif;font-size:14px;font-weight:normal;overflow:hidden;padding:10px 5px;word-break:normal;}
.tg .tg-hmp3{background-color:#D2E4FC;text-align:left;vertical-align:top}
.tg .' + @sHtmlClassId1 + N'{text-align:left;vertical-align:top}
.tg-sort-header::-moz-selection{background:0 0}
.tg-sort-header::selection{background:0 0}.tg-sort-header{cursor:pointer}
.tg-sort-header:after{content:'''';float:right;margin-top:7px;border-width:0 5px 5px;border-style:solid;
border-color:#404040 transparent;visibility:hidden}
.tg-sort-header:hover:after{visibility:visible}
.tg-sort-asc:after,.tg-sort-asc:hover:after,.tg-sort-desc:after{visibility:visible;opacity:.4}
.tg-sort-desc:after{border-bottom:none;border-width:5px 5px 0}</style>'
SET @sHtmlTableRowColTemplate1 = N'<td class="tg-hmp3">||$ColInnerFieldContent$||</td>'
SET @sHtmlTableRowColTemplate2 = N'<td class="' + @sHtmlClassId1 + N'">||$ColInnerFieldContent$||</td>'
END
IF @nStyle = 2
BEGIN
SET @sHtmlCss = N'<style type="text/css">
.tg {border-collapse:collapse;border-color:#aaa;border-spacing:0;}
.tg td{background-color:#fff;border-color:#aaa;border-style:solid;border-width:1px;color:#333;
font-family:Arial, sans-serif;font-size:14px;overflow:hidden;padding:10px 5px;word-break:normal;}
.tg th{background-color:#f38630;border-color:#aaa;border-style:solid;border-width:1px;color:#fff;
font-family:Arial, sans-serif;font-size:14px;font-weight:normal;overflow:hidden;padding:10px 5px;word-break:normal;}
.tg .' + @sHtmlClassId1 + N'{text-align:left;vertical-align:top}
.tg .tg-dg7a{background-color:#FCFBE3;text-align:left;vertical-align:top}
.tg-sort-header::-moz-selection{background:0 0}
.tg-sort-header::selection{background:0 0}.tg-sort-header{cursor:pointer}
.tg-sort-header:after{content:'''';float:right;margin-top:7px;border-width:0 5px 5px;border-style:solid;
border-color:#404040 transparent;visibility:hidden}
.tg-sort-header:hover:after{visibility:visible}
.tg-sort-asc:after,.tg-sort-asc:hover:after,.tg-sort-desc:after{visibility:visible;opacity:.4}
.tg-sort-desc:after{border-bottom:none;border-width:5px 5px 0}</style>'
SET @sHtmlTableRowColTemplate1 = N'<td class="tg-hmp3">||$ColInnerFieldContent$||</td>'
SET @sHtmlTableRowColTemplate2 = N'<td class="' + @sHtmlClassId1 + N'">||$ColInnerFieldContent$||</td>'
END
IF @nStyle = 3
BEGIN
SET @sHtmlCss = N'<style type="text/css">
.tg {border-collapse:collapse;border-color:#93a1a1;border-spacing:0;}
.tg td{background-color:#fdf6e3;border-color:#93a1a1;border-style:solid;border-width:1px;color:#002b36;
font-family:Arial, sans-serif;font-size:14px;overflow:hidden;padding:10px 5px;word-break:normal;}
.tg th{background-color:#657b83;border-color:#93a1a1;border-style:solid;border-width:1px;color:#fdf6e3;
font-family:Arial, sans-serif;font-size:14px;font-weight:normal;overflow:hidden;padding:10px 5px;word-break:normal;}
.tg .tg-alz1{background-color:#eee8d5;text-align:left;vertical-align:top}
.tg .' + @sHtmlClassId1 + N'{text-align:left;vertical-align:top}
.tg-sort-header::-moz-selection{background:0 0}
.tg-sort-header::selection{background:0 0}.tg-sort-header{cursor:pointer}
.tg-sort-header:after{content:'''';float:right;margin-top:7px;border-width:0 5px 5px;border-style:solid;
border-color:#404040 transparent;visibility:hidden}
.tg-sort-header:hover:after{visibility:visible}
.tg-sort-asc:after,.tg-sort-asc:hover:after,.tg-sort-desc:after{visibility:visible;opacity:.4}
.tg-sort-desc:after{border-bottom:none;border-width:5px 5px 0}</style>'
SET @sHtmlTableRowColTemplate1 = N'<td class="tg-hmp3">||$ColInnerFieldContent$||</td>'
SET @sHtmlTableRowColTemplate2 = N'<td class="' + @sHtmlClassId1 + N'">||$ColInnerFieldContent$||</td>'
END
IF @nStyle = 4
BEGIN
SET @sHtmlCss = N'<style type="text/css">
.tg {border-collapse:collapse;border-color:#aabcfe;border-spacing:0;}
.tg td{background-color:#e8edff;border-color:#aabcfe;border-style:solid;border-width:1px;color:#669;
font-family:Arial, sans-serif;font-size:14px;overflow:hidden;padding:10px 5px;word-break:normal;}
.tg th{background-color:#b9c9fe;border-color:#aabcfe;border-style:solid;border-width:1px;color:#039;
font-family:Arial, sans-serif;font-size:14px;font-weight:normal;overflow:hidden;padding:10px 5px;word-break:normal;}
.tg .tg-hmp3{background-color:#D2E4FC;text-align:left;vertical-align:top}
.tg .' + @sHtmlClassId1 + N'{text-align:left;vertical-align:top}
.tg-sort-header::-moz-selection{background:0 0}
.tg-sort-header::selection{background:0 0}.tg-sort-header{cursor:pointer}
.tg-sort-header:after{content:'''';float:right;margin-top:7px;border-width:0 5px 5px;border-style:solid;
border-color:#404040 transparent;visibility:hidden}
.tg-sort-header:hover:after{visibility:visible}
.tg-sort-asc:after,.tg-sort-asc:hover:after,.tg-sort-desc:after{visibility:visible;opacity:.4}
.tg-sort-desc:after{border-bottom:none;border-width:5px 5px 0}</style>'
SET @sHtmlTableRowColTemplate1 = N'<td class="tg-hmp3">||$ColInnerFieldContent$||</td>'
SET @sHtmlTableRowColTemplate2 = N'<td class="' + @sHtmlClassId1 + N'">||$ColInnerFieldContent$||</td>'
END
IF @nStyle = 5
BEGIN
SET @sHtmlCss = N'<style type="text/css">
.tg {border-collapse:collapse;border-color:#ccc;border-spacing:0;}
.tg td{background-color:#fff;border-color:#ccc;border-style:solid;border-width:1px;color:#333;
font-family:Arial, sans-serif;font-size:14px;overflow:hidden;padding:10px 5px;word-break:normal;}
.tg th{background-color:#f0f0f0;border-color:#ccc;border-style:solid;border-width:1px;color:#333;
font-family:Arial, sans-serif;font-size:14px;font-weight:normal;overflow:hidden;padding:10px 5px;word-break:normal;}
.tg .tg-buh4{background-color:#f9f9f9;text-align:left;vertical-align:top}
.tg .' + @sHtmlClassId1 + N'{text-align:left;vertical-align:top}
.tg-sort-header::-moz-selection{background:0 0}
.tg-sort-header::selection{background:0 0}.tg-sort-header{cursor:pointer}
.tg-sort-header:after{content:'''';float:right;margin-top:7px;border-width:0 5px 5px;border-style:solid;
border-color:#404040 transparent;visibility:hidden}
.tg-sort-header:hover:after{visibility:visible}
.tg-sort-asc:after,.tg-sort-asc:hover:after,.tg-sort-desc:after{visibility:visible;opacity:.4}
.tg-sort-desc:after{border-bottom:none;border-width:5px 5px 0}</style>'
SET @sHtmlTableRowColTemplate1 = N'<td class="tg-hmp3">||$ColInnerFieldContent$||</td>'
SET @sHtmlTableRowColTemplate2 = N'<td class="' + @sHtmlClassId1 + N'">||$ColInnerFieldContent$||</td>'
END
IF @nStyle = 6
BEGIN
SET @sHtmlCss = N'<style type="text/css">
.tg {border-collapse:collapse;border-color:#bbb;border-spacing:0;}
.tg td{background-color:#E0FFEB;border-color:#bbb;border-style:solid;border-width:1px;color:#594F4F;
font-family:Arial, sans-serif;font-size:14px;overflow:hidden;padding:10px 5px;word-break:normal;}
.tg th{background-color:#9DE0AD;border-color:#bbb;border-style:solid;border-width:1px;color:#493F3F;
font-family:Arial, sans-serif;font-size:14px;font-weight:normal;overflow:hidden;padding:10px 5px;word-break:normal;}
.tg .' + @sHtmlClassId1 + N'{text-align:left;vertical-align:top}
.tg .tg-sjuo{background-color:#C2FFD6;text-align:left;vertical-align:top}
.tg-sort-header::-moz-selection{background:0 0}
.tg-sort-header::selection{background:0 0}.tg-sort-header{cursor:pointer}
.tg-sort-header:after{content:'''';float:right;margin-top:7px;border-width:0 5px 5px;border-style:solid;
border-color:#404040 transparent;visibility:hidden}
.tg-sort-header:hover:after{visibility:visible}
.tg-sort-asc:after,.tg-sort-asc:hover:after,.tg-sort-desc:after{visibility:visible;opacity:.4}
.tg-sort-desc:after{border-bottom:none;border-width:5px 5px 0}</style>'
SET @sHtmlTableRowColTemplate1 = N'<td class="tg-hmp3">||$ColInnerFieldContent$||</td>'
SET @sHtmlTableRowColTemplate2 = N'<td class="' + @sHtmlClassId1 + N'">||$ColInnerFieldContent$||</td>'
END
IF @bTableAllowSort <> 0
BEGIN
SET @sHtmlSortScript = N'<script charset="utf-8">var TGSort=window.TGSort||function(n){"use strict";function r(n){return n?n.length:0}function t(n,t,e,o=0){for(e=r(n);o<e;++o)t(n[o],o)}function e(n){return n.split("").reverse().join("")}function o(n){var e=n[0];return t(n,function(n){for(;!n.startsWith(e);)e=e.substring(0,r(e)-1)}),r(e)}function u(n,r,e=[]){return t(n,function(n){r(n)&&e.push(n)}),e}var a=parseFloat;function i(n,r){return function(t){var e="";return t.replace(n,function(n,t,o){return e=t.replace(r,"")+"."+(o||"").substring(1)}),a(e)}}var s=i(/^(?:\s*)([+-]?(?:\d+)(?:,\d{3})*)(\.\d*)?$/g,/,/g),c=i(/^(?:\s*)([+-]?(?:\d+)(?:\.\d{3})*)(,\d*)?$/g,/\./g);function f(n){var t=a(n);return!isNaN(t)&&r(""+t)+1>=r(n)?t:NaN}function d(n){var e=[],o=n;return t([f,s,c],function(u){var a=[],i=[];t(n,function(n,r){r=u(n),a.push(r),r||i.push(n)}),r(i)<r(o)&&(o=i,e=a)}),r(u(o,function(n){return n==o[0]}))==r(o)?e:[]}function v(n){if("TABLE"==n.nodeName){for(var a=function(r){var e,o,u=[],a=[];return function n(r,e){e(r),t(r.childNodes,function(r){n(r,e)})}(n,function(n){"TR"==(o=n.nodeName)?(e=[],u.push(e),a.push(n)):"TD"!=o&&"TH"!=o||e.push(n)}),[u,a]}(),i=a[0],s=a[1],c=r(i),f=c>1&&r(i[0])<r(i[1])?1:0,v=f+1,p=i[f],h=r(p),l=[],g=[],N=[],m=v;m<c;++m){for(var T=0;T<h;++T){r(g)<h&&g.push([]);var C=i[m][T],L=C.textContent||C.innerText||"";g[T].push(L.trim())}N.push(m-v)}t(p,function(n,t){l[t]=0;var a=n.classList;a.add("tg-sort-header"),n.addEventListener("click",function(){var n=l[t];!function(){for(var n=0;n<h;++n){var r=p[n].classList;r.remove("tg-sort-asc"),r.remove("tg-sort-desc"),l[n]=0}}(),(n=1==n?-1:+!n)&&a.add(n>0?"tg-sort-asc":"tg-sort-desc"),l[t]=n;var i,f=g[t],m=function(r,t){return n*f[r].localeCompare(f[t])||n*(r-t)},T=function(n){var t=d(n);if(!r(t)){var u=o(n),a=o(n.map(e));t=d(n.map(function(n){return n.substring(u,r(n)-a)}))}return t}(f);(r(T)||r(T=r(u(i=f.map(Date.parse),isNaN))?[]:i))&&(m=function(r,t){var e=T[r],o=T[t],u=isNaN(e),a=isNaN(o);return u&&a?0:u?-n:a?n:e>o?n:e<o?-n:n*(r-t)});var C,L=N.slice();L.sort(m);for(var E=v;E<c;++E)(C=s[E].parentNode).removeChild(s[E]);for(E=v;E<c;++E)C.appendChild(s[v+L[E-v]])})})}}n.addEventListener("DOMContentLoaded",function(){for(var t=n.getElementsByClassName("tg"),e=0;e<r(t);++e)try{v(t[e])}catch(n){}})}(document)</script>'
END
SET @sHtmlTableHeaderCaptionTemplate = N'<th class="' + @sHtmlClassId1 + N'">||$ColName$||</th>' + NCHAR(13) + NCHAR(10)
SET @sHtmlTableRowTemplate = N' <tr>
||$TblRowAllColumnsContent$||
</tr>'
SET @sHtmlTableOuter = @sHtmlCss + NCHAR(13) + NCHAR(10) +
N'<table id="' + @sTableHtmlId + N'" class="tg">
<thead>
<tr>
||$TblHeaderCol$||
</tr>
</thead>
<tbody>
||$TblRows$||
</tbody>
</table>
'
-- Determine table captions
DECLARE @lColCountHeaders INT
SET @lColCountHeaders = 0
SET @sHtmlTableHeaders = N''
DECLARE @sCaption nvarchar(max)
IF CHARINDEX(N';', @sTableHeaderCaptions) = 0
BEGIN
SET @sTableHeaderCaptions = ISNULL(@sTableHeaderCaptions, N'') + N';'
END
DECLARE cSAGHtmlHelpersGetHtmlTableDataCaptions CURSOR FOR
SELECT [TextPart] FROM [dbo].[gtblSAG_Get_Text_Split] ( @sTableHeaderCaptions , N';' )
WHERE LTRIM(RTRIM(ISNULL([TextPart], N''))) <> N''
ORDER BY [Nr]
OPEN cSAGHtmlHelpersGetHtmlTableDataCaptions
IF @@Cursor_Rows <> 0
BEGIN
FETCH NEXT FROM cSAGHtmlHelpersGetHtmlTableDataCaptions INTO @sCaption
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
SET @lColCountHeaders = @lColCountHeaders + 1
IF @bTableHeaderCaptionsContainHtml = 0
BEGIN
SET @sCaption = ( SELECT TOP 1 ISNULL(@sCaption, N'(undefined)') FOR XML PATH('') )
END
SET @sHtmlTableHeaders = @sHtmlTableHeaders + REPLACE(@sHtmlTableHeaderCaptionTemplate, N'||$ColName$||', @sCaption)
FETCH NEXT FROM cSAGHtmlHelpersGetHtmlTableDataCaptions INTO @sCaption
END
CLOSE cSAGHtmlHelpersGetHtmlTableDataCaptions
END
DEALLOCATE cSAGHtmlHelpersGetHtmlTableDataCaptions
IF @lColCountHeaders = 0
BEGIN
SET @sReturnHtml = N'<html>SAG: 202111175: ' + @sProcedure + N': ' + @sErrorTableHeaderCaptions
RETURN 2
END
-- Table definition
SET @sHtmlTableOuter = REPLACE(@sHtmlTableOuter, N'||$TblHeaderCol$||', @sHtmlTableHeaders)
IF LTRIM(RTRIM(ISNULL(@sHtmlSortScript, N''))) <> N''
BEGIN
SET @sHtmlTableOuter = @sHtmlTableOuter + NCHAR(13) + NCHAR(10) + @sHtmlSortScript
END
SET @sReturnHtml = @sHtmlTableOuter
-- Build own Sql column clause
DECLARE @lColCount INT
DECLARE @sColSep NVARCHAR(10)
DECLARE @sColSepBugFixReplacement NVARCHAR(10)
DECLARE @sColAll NVARCHAR(MAX)
SET @lColCount = 0
SET @sColSep = N'||_||||_||'
-- Note, the method 'gtblSAG_Get_Text_Split' got a bug in some versions, where parts of the replacement will be added as content:
SET @sColSepBugFixReplacement = N'|_||||_||'
SET @sColAll = N''
IF CHARINDEX(N',', @sSqlOnlyFields) = 0
BEGIN
SET @sSqlOnlyFields = ISNULL(@sSqlOnlyFields, N'') + N','
END
DECLARE cSAGHtmlHelpersGetHtmlTableDataColumns CURSOR FOR
SELECT [TextPart] FROM [dbo].[gtblSAG_Get_Text_Split] ( @sSqlOnlyFields , N',' )
WHERE LTRIM(RTRIM(ISNULL([TextPart], N''))) <> N''
ORDER BY [Nr]
OPEN cSAGHtmlHelpersGetHtmlTableDataColumns
IF @@Cursor_Rows <> 0
BEGIN
DECLARE @sCol AS NVARCHAR(1025)
DECLARE @lIdx INT
FETCH NEXT FROM cSAGHtmlHelpersGetHtmlTableDataColumns INTO @sCol
SET @lIdx = 0
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
SET @lColCount = @lColCount + 1
SET @lIdx = @lIdx + 1
SET @sCol = REPLACE(REPLACE(LTRIM(RTRIM(ISNULL(@sCol, N''))), N'[', N''), N']', N'')
IF @sCol <> N''
BEGIN
IF @lIdx > 1
BEGIN
SET @sColAll = ISNULL(@sColAll, N'') + N' + ' + NCHAR(39) + @sColSep + NCHAR(39) + N' + '
END
SET @sColAll = ISNULL(@sColAll, N'') + N'CAST(ISNULL([' + @sCol + N'], N' + NCHAR(39) + NCHAR(39) + N') AS NVARCHAR(MAX))'
END
FETCH NEXT FROM cSAGHtmlHelpersGetHtmlTableDataColumns INTO @sCol
END
CLOSE cSAGHtmlHelpersGetHtmlTableDataColumns
END
DEALLOCATE cSAGHtmlHelpersGetHtmlTableDataColumns
IF @lColCount <> @lColCountHeaders
BEGIN
SET @sReturnHtml = N'<html>SAG: 2021111719: ' + @sProcedure + N': Column count missmatch! '
+ N'Provided header columns: ' + CAST(@lColCountHeaders AS nvarchar(50)) + N'. '
+ N'Provided data columns: ' + CAST(@lColCount AS nvarchar(50)) + N'. '
RETURN 6
END
IF LTRIM(RTRIM(ISNULL(@sColAll, N''))) <> N''
BEGIN
SET @sColAll = @sColAll + N' AS [DataBigCol] '
SET @sSqlSelect = N'SELECT '
IF @lOptionalTopRecord > 0
BEGIN
SET @sSqlSelect = @sSqlSelect + N' TOP ' + CAST(@lOptionalTopRecord AS NVARCHAR(50)) + N' '
END
SET @sSqlSelect = @sSqlSelect + @sColAll + N' ' + @sSqlOnlyFromClause
-- PRINT @sSqlSelect
-- Get the actual data
DECLARE @lRC INT
DECLARE @sSQL NVARCHAR(MAX)
-- Define cursor
SET @sSQL = N'DECLARE cSAGHtmlHelpersGetHtmlTableData CURSOR FOR ' + N' ' + @sSqlSelect + N' '
EXEC @lRC = sp_executesql @sSQL
IF @lRC <> 0
BEGIN
SET @sReturnHtml = N'<html>SAG: 2021111710: ' + @sProcedure + N': ' + CAST(ISNULL(@lRC, 0) AS NVARCHAR(50)) + N': Error while executing the SQL statement: ' + ISNULL(@sSQL, N'')
RETURN 7
END
-- Open cursor
OPEN cSAGHtmlHelpersGetHtmlTableData
SET @sHtmlTableRows = N''
IF @@Cursor_Rows<>0
BEGIN
DECLARE @lRow INT
DECLARE @lCol INT
DECLARE @sAllData NVARCHAR(MAX)
DECLARE @sAllDataReal NVARCHAR(MAX)
DECLARE @sHtmlTableRow NVARCHAR(MAX)
DECLARE @sHtmlTableCol NVARCHAR(MAX)
DECLARE @sHtmlTableColAll NVARCHAR(MAX)
DECLARE @sHtmlTableColTemplInner NVARCHAR(MAX)
-- Select first record
FETCH NEXT FROM cSAGHtmlHelpersGetHtmlTableData INTO @sAllData
SET @lRow = 0
SET @lIdx = 0
-- Loop all records
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
SET @sAllData = ISNULL(@sAllData, N'')
SET @lRow = @lRow + 1
SET @lIdx = @lIdx + 1
IF @lIdx = 1
BEGIN
SET @sHtmlTableColTemplInner = @sHtmlTableRowColTemplate1
END
IF @lIdx = 2
BEGIN
SET @sHtmlTableColTemplInner = @sHtmlTableRowColTemplate2
END
IF @lIdx >= 2 SET @lIdx = 0
SET @sHtmlTableColAll = N''
-- Main work
IF CHARINDEX(@sColSep, @sAllData) = 0
BEGIN
SET @sAllData = ISNULL(@sAllData, N'') + @sColSep
END
DECLARE cSAGHtmlHelpersGetHtmlTableDataInner CURSOR FOR
SELECT [TextPart] FROM [dbo].[gtblSAG_Get_Text_Split] ( @sAllData , @sColSep )
OPEN cSAGHtmlHelpersGetHtmlTableDataInner
IF @@Cursor_Rows <> 0
BEGIN
FETCH NEXT FROM cSAGHtmlHelpersGetHtmlTableDataInner INTO @sAllDataReal
SET @lCol = 0
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
SET @lCol = @lCol + 1
IF @lCol > 1 SET @sHtmlTableColAll = ISNULL(@sHtmlTableColAll, N'') + NCHAR(13) + NCHAR(10)
IF @lCol <= @lColCountHeaders
BEGIN
SET @sAllDataReal = REPLACE(REPLACE(ISNULL(@sAllDataReal, N''), @sColSep, N''), @sColSepBugFixReplacement, N'')
SET @sAllDataReal = ISNULL(@sAllDataReal, N'')
-- This is the real content of the column!
IF @bTableDataContainsHtml = 0
BEGIN
SET @sAllDataReal = ( SELECT TOP 1 ISNULL(@sAllDataReal, N'') FOR XML PATH('') )
END
SET @sHtmlTableCol = REPLACE( @sHtmlTableColTemplInner , N'||$ColInnerFieldContent$||' , @sAllDataReal )
SET @sHtmlTableColAll = ISNULL(@sHtmlTableColAll, N'') + @sHtmlTableCol
END
FETCH NEXT FROM cSAGHtmlHelpersGetHtmlTableDataInner INTO @sAllDataReal
END
CLOSE cSAGHtmlHelpersGetHtmlTableDataInner
END
DEALLOCATE cSAGHtmlHelpersGetHtmlTableDataInner
SET @sHtmlTableColAll = ISNULL(@sHtmlTableColAll, N'')
IF @lRow > 1 SET @sHtmlTableRows = ISNULL(@sHtmlTableRows, N'') + NCHAR(13) + NCHAR(10)
SET @sHtmlTableRows = ISNULL(@sHtmlTableRows, N'') + REPLACE(@sHtmlTableRowTemplate, N'||$TblRowAllColumnsContent$||', @sHtmlTableColAll)
-- Next record
FETCH NEXT FROM cSAGHtmlHelpersGetHtmlTableData INTO @sAllData
END
-- Close cursor
CLOSE cSAGHtmlHelpersGetHtmlTableData
END
ELSE
BEGIN
SET @sHtmlTableRows = ISNULL(@sHtmlTableRows, N'') + NCHAR(13) + NCHAR(10)
END
-- Free cursor
DEALLOCATE cSAGHtmlHelpersGetHtmlTableData
END
ELSE
BEGIN
SET @sHtmlTableRows = ISNULL(@sHtmlTableRows, N'') + NCHAR(13) + NCHAR(10)
END
SET @sHtmlTableOuter = REPLACE(@sHtmlTableOuter, N'||$TblRows$||', @sHtmlTableRows)
SET @sReturnHtml = @sHtmlTableOuter
RETURN 0
GObottom of page