-
Notifications
You must be signed in to change notification settings - Fork 4
/
dbo.FilterParseTVPParameters.sql
177 lines (156 loc) · 5.71 KB
/
dbo.FilterParseTVPParameters.sql
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
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
/*
Fully Parameterized Search Query
--------------------------------
Copyright Eitan Blumin (c) 2018; email: eitan@madeiradata.com
You may use the contents of this SQL script or parts of it, modified or otherwise
for any purpose that you wish (including commercial).
Under the single condition that you include in the script
this comment block unchanged, and the URL to the original source, which is:
http://www.eitanblumin.com/
--------------------------------
Example Usage:
--------------------------------
DECLARE @SQL NVARCHAR(MAX), @TVPParams dbo.UDT_FilterParameters, @TVPOrdering dbo.UDT_ColumnOrder
INSERT INTO @TVPParams
(ColumnID, OperatorID, [Value])
VALUES
(1, 11, N'2'),
(2, 11, N'DB1'),
(2, 11, N'DB2'),
(3, 6, N'2018-11-11 15:00')
INSERT INTO @TVPOrdering
(ColumnOrdinal, ColumnID, IsAscending)
VALUES
(1, 11, 1),
(2, 5, 1)
EXEC dbo.FilterParseTVPParameters @SourceTableAlias = 'Members', @TVPParams = @TVPParams, @TVPOrdering = @TVPOrdering, @ParsedSQL = @SQL OUTPUT
PRINT @SQL
EXEC sp_executesql @SQL, N'@TVPParams dbo.UDT_FilterParametersas READONLY', @TVPParams
*/
CREATE PROCEDURE [dbo].[FilterParseTVPParameters]
@SourceTableAlias SYSNAME, -- the alias of the table from FilterTables to be used as the source
@TVPParams dbo.UDT_FilterParameters READONLY, -- the TVP definition of the parameter values
@TVPOrdering dbo.UDT_ColumnOrder READONLY, -- the TVP definition of the column ordering (optional)
@PageSize INT = 9999,
@Offset INT = 1,
@ParsedSQL NVARCHAR(MAX) OUTPUT, -- returns the parsed SQL command to be used for sp_executesql.
@ForceRecompile BIT = 0, -- forces the query to do parameter sniffing using OPTION(RECOMPILE)
@RowNumberColumn SYSNAME = 'RowNumber', -- you can optionally change the name of the RowNumber column used for pagination (to avoid collision with existing columns)
@RunCommand BIT = 0 -- determines whether to run the parsed command (otherwise just output the command w/o running it)
AS BEGIN
SET XACT_ABORT ON;
SET ARITHABORT ON;
SET NOCOUNT ON;
-- Init variables
DECLARE
@SourceTableName SYSNAME,
@PageOrdering NVARCHAR(MAX),
@FilterString NVARCHAR(MAX),
@FilterTablesString NVARCHAR(MAX),
@FilterParamInit NVARCHAR(4000)
SET @FilterString = N'';
SET @FilterTablesString = N'';
SELECT @SourceTableName = FilterTableName
FROM FilterTables
WHERE FilterTableAlias = @SourceTableAlias
IF @SourceTableName IS NULL
BEGIN
RAISERROR(N'Table %s was not found in definitions',16,1,@SourceTableAlias);
RETURN -1;
END
-- Prepare the ORDER BY clause (save in indexed temp table to ensure sort which might be distorted by the JOIN otherwise)
DECLARE @SortedColumns AS TABLE (ColumnRealName SYSNAME, IsAscending BIT, ColumnIndex BIGINT PRIMARY KEY);
INSERT INTO @SortedColumns
SELECT
FilterColumns.ColumnRealName, Q.IsAscending, Q.ColumnIndex
FROM
(
SELECT
ColumnIndex = ColumnOrdinal,
OrderingColumnID = ColumnID,
IsAscending = IsAscending
FROM
@TVPOrdering
) AS Q
JOIN
FilterColumns
ON
Q.OrderingColumnID = FilterColumns.ColumnID
INNER JOIN
FilterTables
ON
FilterColumns.ColumnFilterTableAlias = FilterTables.FilterTableAlias
WHERE
FilterColumns.ColumnSortEnabled = 1
AND FilterColumns.ColumnFilterTableAlias = @SourceTableAlias
SELECT
@PageOrdering = ISNULL(@PageOrdering + N', ',N'') + ColumnRealName + N' ' + CASE WHEN IsAscending = 1 THEN 'ASC' ELSE 'DESC' END
FROM @SortedColumns
IF @PageOrdering IS NULL
SET @PageOrdering = '(SELECT NULL)'
-- Parse filtering
SELECT
@FilterParamInit = ISNULL(@FilterParamInit, '') + N'
DECLARE @p' + ParamIndex +
-- If Operator is multi-valued, declare local variable as a temporary table, to ensure strong-typing
CASE WHEN FilterOperators.IsMultiValue = 1 THEN
N' TABLE ([Value] ' + FilterColumns.ColumnSqlDataType + N');
INSERT INTO @p' + ParamIndex + N'
SELECT CONVERT(' + FilterColumns.ColumnSqlDataType + N', [value])
FROM @TVPParams
WHERE ParamIndex = ' + ParamIndex + N';
'
-- If Operator is single-valued, declare the local variable as a regular variable, to ensure strong-typing.
ELSE
N' ' + FilterColumns.ColumnSqlDataType + N';
SELECT @p' + ParamIndex + N' = CONVERT(' + FilterColumns.ColumnSqlDataType + N', [value]) FROM @TVPParams WHERE ParamIndex = ' + ParamIndex + N';
'
END
,
-- Parse the Operator template by replacing the placeholders
@FilterString = @FilterString + N'
AND ' + REPLACE(
REPLACE(
FilterOperators.OperatorTemplate
, '{Column}',FilterColumns.ColumnRealName)
, '{Parameter}', '@p' + ParamIndex)
FROM
(
SELECT DISTINCT
ParamIndex = CONVERT(nvarchar(max), ParamIndex) COLLATE database_default,
FilterColumnID = ColumnId,
FilterOperatorID = OperatorID
FROM
@TVPParams
) AS ParamValues
JOIN
FilterColumns
ON
ParamValues.FilterColumnID = FilterColumns.ColumnID
JOIN
FilterOperators
ON
ParamValues.FilterOperatorID = FilterOperators.OperatorID
INNER JOIN
FilterTables
ON
FilterColumns.ColumnFilterTableAlias = FilterTables.FilterTableAlias
WHERE
FilterColumns.ColumnFilterTableAlias = @SourceTableAlias
-- Construct the final parsed SQL command
SET @ParsedSQL = ISNULL(@FilterParamInit, '') + N'
SELECT * FROM
(SELECT Main.*, ' + QUOTENAME(@RowNumberColumn) + N' = ROW_NUMBER() OVER( ORDER BY ' + @PageOrdering + N' )
FROM ' + @SourceTableName + N' AS Main
WHERE 1=1 ' + ISNULL(@FilterString,'') + N'
) AS Q
WHERE '+ QUOTENAME(@RowNumberColumn) + N' BETWEEN ' + CONVERT(nvarchar(50), @Offset) + N' AND ' + CONVERT(nvarchar(50), @Offset + @PageSize - 1) + N'
ORDER BY ' + QUOTENAME(@RowNumberColumn);
-- Optionally add RECOMPILE hint
IF @ForceRecompile = 1
SET @ParsedSQL = @ParsedSQL + N'
OPTION (RECOMPILE)'
-- Optionally run the command
IF @RunCommand = 1
EXEC sp_executesql @ParsedSQL, N'@TVPParams dbo.UDT_FilterParameters READONLY', @TVPParams
END