-
Notifications
You must be signed in to change notification settings - Fork 1
Expand file tree
/
Copy pathdbo.udtf_html_datatypes.sql
More file actions
114 lines (110 loc) · 11.5 KB
/
Copy pathdbo.udtf_html_datatypes.sql
File metadata and controls
114 lines (110 loc) · 11.5 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
USE [NBA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*------------------------------------------------------------------------------
Function Name: [dbo].[udtf_html_datatypes]
Purpose: Returns a mapping of SQL data types to display metadata for HTML rendering
+
+
Project: T-SQL-Scripts
+
Date Created: 2025-10-15
+
Author: Ludwig Mark
+
Parameters:
+
+
+ @input - NVARCHAR(MAX) - (not used) placeholder for future extensions
+
+
+ Returns: TABLE (type_id INT, type_name VARCHAR(128), sys_id INT, usr_id INT, html_type VARCHAR(128), exclude BIT, html_align VARCHAR(128), dsql_cast VARCHAR(128), alias VARCHAR(128))
+
+
+ Notes:
+
+ - Static mapping useful for HTML generation of result sets
+
+ Performance:
+
+ - Execution time: O(1)
+
+
+ Dependencies:
+
+ - No external dependencies
+
+
+ Example:
+
+ ------------------------
+
+ SELECT * FROM dbo.udtf_html_datatypes(NULL)
+
+
+ Modification History:
+
+
+ Date Author Description
+
+ ---------- ---------- ------------------------------------------------
+
+ 2025-10-15 Ludwig M. Initial creation
+
+
+ LICENSE: MIT
+
+------------------------------------------------------------------------------*/
CREATE FUNCTION [dbo].[udtf_html_datatypes](@input NVARCHAR(MAX))
RETURNS @html_types TABLE (
type_id INT IDENTITY(1,1),
type_name VARCHAR(128),
sys_id INT,
usr_id INT,
html_type VARCHAR(128),
exclude BIT,
html_align VARCHAR(128),
dsql_cast VARCHAR(128),
alias VARCHAR(128)
)
AS BEGIN
INSERT INTO @html_types SELECT 'text', 35, 35, 'text', 0, 'left', 'ISNULL(CONVERT(NVARCHAR(MAX), ${data}), '''')' , 'text'
INSERT INTO @html_types SELECT 'date', 40, 40, 'date', 0, 'center', 'ISNULL(CONVERT(NVARCHAR(23), ${data}, 126), '''')' , 'date'
INSERT INTO @html_types SELECT 'time', 41, 41, 'time', 0, 'left', 'ISNULL(CONVERT(NVARCHAR(23), ${data}, 126), '''')' , 'date'
INSERT INTO @html_types SELECT 'datetime2', 42, 42, 'datetime', 0, 'center', 'ISNULL(CONVERT(NVARCHAR(23), ${data}, 126), '''')' , 'date'
INSERT INTO @html_types SELECT 'tinyint', 48, 48, 'number', 0, 'right', 'REPLACE(FORMAT(ISNULL(${data},0), ''C''), ''$'', '''')' , 'number'
INSERT INTO @html_types SELECT 'smallint', 52, 52, 'number', 0, 'right', 'REPLACE(FORMAT(ISNULL(${data},0), ''C''), ''$'', '''')' , 'number'
INSERT INTO @html_types SELECT 'int', 56, 56, 'number', 0, 'right', 'REPLACE(FORMAT(ISNULL(${data},0), ''C''), ''$'', '''')' , 'number'
INSERT INTO @html_types SELECT 'smalldatetime', 58, 58, 'datetime', 0, 'center', 'ISNULL(CONVERT(NVARCHAR(23), ${data}, 126), '')' , 'date'
INSERT INTO @html_types SELECT 'real', 59, 59, 'number', 0, 'right', 'REPLACE(FORMAT(ISNULL(${data},0), ''C''), ''$'', '''')' , 'number'
INSERT INTO @html_types SELECT 'money', 60, 60, 'number', 0, 'left', 'REPLACE(FORMAT(ISNULL(${data},0), ''C''), ''$'', '''')' , 'currency'
INSERT INTO @html_types SELECT 'datetime', 61, 61, 'datetime', 0, 'center', 'ISNULL(CONVERT(NVARCHAR(23), ${data}, 126), '')' , 'date'
INSERT INTO @html_types SELECT 'float', 62, 62, 'number', 0, 'right', 'REPLACE(FORMAT(ISNULL(${data},0), ''C''), ''$'', '''')' , 'currency'
INSERT INTO @html_types SELECT 'sql_variant', 98, 98, 'text', 0, 'left', 'ISNULL(CONVERT(NVARCHAR(MAX), ${data}), '''')' , 'text'
INSERT INTO @html_types SELECT 'ntext', 99, 99, 'text', 0, 'left', 'ISNULL(CONVERT(NVARCHAR(MAX), ${data}), '''')' , 'text'
INSERT INTO @html_types SELECT 'bit', 104, 104, 'bool', 0, 'center', 'REPLACE(FORMAT(ISNULL(${data},0), ''C''), ''$'', '''')' , 'number'
INSERT INTO @html_types SELECT 'decimal', 106, 106, 'number', 0, 'left', 'REPLACE(FORMAT(ISNULL(${data},0), ''C''), ''$'', '''')' , 'currency'
INSERT INTO @html_types SELECT 'numeric', 108, 108, 'number', 0, 'left', 'REPLACE(FORMAT(ISNULL(${data},0), ''C''), ''$'', '''')' , 'currency'
INSERT INTO @html_types SELECT 'smallmoney', 122, 122, 'number', 0, 'left', 'REPLACE(FORMAT(ISNULL(${data},0), ''C''), ''$'', '''')' , 'currency'
INSERT INTO @html_types SELECT 'bigint', 127, 127, 'number', 0, 'right', 'REPLACE(FORMAT(ISNULL(${data},0), ''C''), ''$'', '''')' , 'number'
INSERT INTO @html_types SELECT 'varchar', 167, 167, 'text', 0, 'left', 'ISNULL(CONVERT(NVARCHAR(MAX), ${data}), '''')' , 'text'
INSERT INTO @html_types SELECT 'char', 175, 175, 'text', 0, 'left', 'ISNULL(CONVERT(NVARCHAR(MAX), ${data}), '''')' , 'text'
INSERT INTO @html_types SELECT 'timestamp', 189, 189, 'datetime', 0, 'center', 'ISNULL(CONVERT(NVARCHAR(23), ${data}, 126), '''')' , 'date'
INSERT INTO @html_types SELECT 'nvarchar', 231, 231, 'text', 0, 'left', 'ISNULL(CONVERT(NVARCHAR(MAX), ${data}), '''')' , 'text'
INSERT INTO @html_types SELECT 'nchar', 239, 239, 'text', 0, 'left', 'ISNULL(CONVERT(NVARCHAR(MAX), ${data}), '''')' , 'text'
INSERT INTO @html_types SELECT 'image', 34, 34, NULL, 1, NULL, NULL, NULL
INSERT INTO @html_types SELECT 'uniqueidentifier', 36, 36, NULL, 1, NULL, NULL, NULL
INSERT INTO @html_types SELECT 'datetimeoffset', 43, 43, NULL, 1, NULL, NULL, NULl
INSERT INTO @html_types SELECT 'hierarchyid', 240, 128, NULL, 1, NULL, NULL, NULL
INSERT INTO @html_types SELECT 'geometry', 240, 129, NULL, 1, NULL, NULL, NULL
INSERT INTO @html_types SELECT 'geography', 240, 130, NULL, 1, NULL, NULL, NULL
INSERT INTO @html_types SELECT 'varbinary', 165, 165, NULL, 1, NULL, NULL, NULL
INSERT INTO @html_types SELECT 'binary', 173, 173, NULL, 1, NULL, NULL, NULL
INSERT INTO @html_types SELECT 'xml', 241, 241, NULL, 1, NULL, NULL, NULL
INSERT INTO @html_types SELECT 'sysname', 231, 256, NULL, 1, NULL, NULL, NULL
RETURN
END
GO