Skip to content

Instantly share code, notes, and snippets.

@EitanBlumin
Last active September 17, 2020 02:05
Show Gist options
  • Save EitanBlumin/55c615e26c54b26cc65967e49e80b46e to your computer and use it in GitHub Desktop.
Save EitanBlumin/55c615e26c54b26cc65967e49e80b46e to your computer and use it in GitHub Desktop.
/*
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/
*/
/*
---------------- Tables ----------------
FilterTables
---------------
A logical group of available filter columns. Each group will represent a single database view (possibly de-normalized).
*/
IF OBJECT_ID('FilterTables') IS NOT NULL AND OBJECTPROPERTY(OBJECT_ID('FilterTables'), 'IsTable') = 1
DROP TABLE FilterTables;
GO
CREATE TABLE FilterTables
(
FilterTableAlias SYSNAME NOT NULL PRIMARY KEY,
FilterTableName SYSNAME NOT NULL
)
-- Sample data
INSERT INTO FilterTables
(FilterTableAlias,FilterTableName)
VALUES
('Latest Alerts','CentralMonitor.dbo.VW_LatestMonitoredObjects')
,('All Alerts','CentralMonitor.dbo.VW_AllMonitoredObjects')
,('Monitor Types','CentralMonitor.dbo.VW_MonitoringTypes')
,('Collector Types','CentralCollector.dbo.VW_CollectorTypes')
GO
/*
FilterPredicates
-----------------
This table will contain the list of possible predicates and the template for each.
The templates use "placeholders" such as {Column} and {Parameter} which can later
be easily replaced with relevant values.
{Column} = Placeholder for the column name to be filtered.
{Parameter} = Placeholder for the local parameter that contains the filter data.
*/
IF OBJECT_ID('FilterPredicates') IS NOT NULL AND OBJECTPROPERTY(OBJECT_ID('FilterPredicates'), 'IsTable') = 1
DROP TABLE FilterPredicates;
GO
CREATE TABLE FilterPredicates
(
PredicateID INT PRIMARY KEY,
IsMultiValue BIT NOT NULL,
PredicateName VARCHAR(50) NOT NULL,
PredicateTemplate VARCHAR(4000) NOT NULL
);
INSERT INTO FilterPredicates
VALUES
(1, 0, 'Contains', '{Column} LIKE ''%'' + {Parameter} + ''%''')
,(2, 0, 'NotContains', '{Column} NOT LIKE ''%'' + {Parameter} + ''%''')
,(3, 0, 'StartsWith', '{Column} LIKE {Parameter} + ''%''')
,(4, 0, 'EndsWith', '{Column} LIKE ''%'' + {Parameter}')
,(5, 0, 'GreaterThan', '{Column} > {Parameter}')
,(6, 0, 'GreaterOrEqual', '{Column} >= {Parameter}')
,(7, 0, 'LessThan', '{Column} < {Parameter}')
,(8, 0, 'LessOrEqual', '{Column} <= {Parameter}')
,(9, 0, 'Equals', '{Column} = {Parameter}')
,(10, 0, 'NotEquals', '{Column} <> {Parameter}')
,(11, 1, 'In', '{Column} IN (SELECT Value FROM {Parameter})')
,(12, 1, 'NotIn', '{Column} NOT IN (SELECT Value FROM {Parameter})')
GO
/*
FilterColumns
----------------
This table will map column names from our target table to an ID and a data type.
Using this table, the GUI can identify columns that can be filtered,
and later the database back-end will use the same table for parsing.
The field QueryForAvailableValues accepts a database query that must return 3 columns:
[value] - Will be used for returning the actual value to be used in the predicate template
[label] - Will be used for displaying the label to the front-end user
[group] - If not NULL, will be used for grouping the values into option groups
*/
IF OBJECT_ID('FilterColumns') IS NOT NULL AND OBJECTPROPERTY(OBJECT_ID('FilterColumns'), 'IsTable') = 1
DROP TABLE FilterColumns;
GO
CREATE TABLE FilterColumns
(
ColumnID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
ColumnFilterTableAlias SYSNAME NOT NULL,
ColumnRealName SYSNAME NOT NULL,
ColumnSqlDataType VARCHAR(50) NOT NULL,
ColumnDisplayName NVARCHAR(200) NULL,
ColumnSortEnabled BIT NOT NULL,
ColumnSupportedFilterPredicates VARCHAR(100) NULL,
QueryForAvailableValues VARCHAR(4000) NULL
);
-- Sample data
INSERT INTO FilterColumns
(ColumnFilterTableAlias,ColumnRealName,ColumnSqlDataType,ColumnDisplayName,ColumnSortEnabled,ColumnSupportedFilterPredicates,QueryForAvailableValues)
VALUES
('Latest Alerts', 'OUID', 'int', 'Organization', 1, '11', 'SELECT OUID AS [value], OUName AS [label], NULL AS [group] FROM CentralMonitor.dbo.AssetOUs ORDER BY 2')
,('Latest Alerts', 'ServerName', 'nvarchar(200)', 'Server Name', 1, '11', 'SELECT ServerName AS [value], ServerName AS [label], OUName AS [group] FROM CentralMonitor.dbo.ServersToMonitor AS srv LEFT JOIN CentralMonitor.dbo.AssetOUs AS ou ON srv.OUID = ou.OUID ORDER BY 3, 2')
,('Latest Alerts', 'SinceStartDate', 'datetime', 'First Date and Time', 1, '5, 6, 7, 8, 9, 10', NULL)
,('Latest Alerts', 'LastRunDate', 'datetime', 'Last Date and Time', 1, '5, 6, 7, 8, 9, 10', NULL)
,('Latest Alerts', 'ObjectName', 'nvarchar(300)', 'Alert Title', 1, '1, 2, 3, 4, 9, 10, 11, 12', NULL)
,('Latest Alerts', 'Description', 'nvarchar(max)', 'Alert Description',0, '1, 2, 3, 4, 9, 10, 11, 12', NULL)
,('Latest Alerts', 'SampleID', 'int', 'Sample ID', 1, '9', NULL)
,('Latest Alerts', 'Severity', 'tinyint', 'Severities', 1, '11', 'SELECT Severity AS [value], SeverityName AS [label], NULL AS [group] FROM CentralMonitor.dbo.LUTSeverity ORDER BY 1')
,('Latest Alerts', 'MonitorID', 'int', 'Monitor Type', 1, '11', 'SELECT MonitorID AS [value], MonitorName AS [label], ObjectTypeName AS [group] FROM CentralMonitor.dbo.VW_MonitoringTypes ORDER BY 3, 2')
,('Latest Alerts', 'ExecutedByMonitorSetID', 'int', 'Executed by Monitor Set', 1, '11', 'SELECT MonitorSetID AS [value], MonitorSetName AS [label], NULL AS [group] FROM CentralMonitor.dbo.MonitorSets ORDER BY 2')
,('Latest Alerts', 'ObjectType', 'nvarchar(100)', 'Monitor Group', 1, '11', 'SELECT ObjectType AS [value], ObjectTypeName AS [label], NULL AS [group] FROM CentralMonitor.dbo.LUTObjectTypes ORDER BY 2')
GO
/*
---------------- Stored Procedure ----------------
This is the stored procedure that will perform the parsing itself.
*/
IF OBJECT_ID('FilterParseJsonParameters') IS NOT NULL AND OBJECTPROPERTY(OBJECT_ID('FilterParseJsonParameters'), 'IsProcedure') = 1
DROP PROCEDURE FilterParseJsonParameters;
GO
/*
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), @JsonParams NVARCHAR(MAX) = N'{ "Parameters":
[
{"columnId": "1", "operatorId": "11", "Value": [ "2" ]},
{"columnId": "2", "operatorId": "11", "Value": [ "RTCMLIVEDB3", "TheOptionLiveDB" ] },
{"columnId": "3", "operatorId": "6", "Value": "2018-11-11 15:00"}
]
}', @JsonOrdering NVARCHAR(MAX) = N'{ "OrderingColumns":
[
{"columnId": "11", "isAscending": "1"},
{"columnId": "5", "isAscending": "1"}
] }'
EXEC dbo.FilterParseJsonParameters @SourceTableAlias = 'Latest Alerts', @JsonParams = @JsonParams, @JsonOrdering = @JsonOrdering, @ParsedSQL = @SQL OUTPUT
PRINT @SQL
EXEC sp_executesql @SQL, N'@JsonParams NVARCHAR(MAX)', @JsonParams
*/
CREATE PROCEDURE FilterParseJsonParameters
@SourceTableAlias SYSNAME, -- the alias of the table from FilterTables to be used as the source
@JsonParams NVARCHAR(MAX), -- the JSON definition of the parameter values
@JsonOrdering NVARCHAR(MAX) = NULL, -- the JSON 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 = 1, -- 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)
AS
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)
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 = [key],
OrderingColumnID = CONVERT(int, JSON_VALUE([value], '$.columnId')),
IsAscending = CONVERT(bit, JSON_VALUE([value], '$.isAscending'))
FROM
OPENJSON(@JsonOrdering, '$.OrderingColumns')
) 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 operand is multi-valued, declare local variable as a temporary table
CASE WHEN FilterPredicates.IsMultiValue = 1 THEN
N' TABLE ([Value] ' + FilterColumns.ColumnSqlDataType + N');
INSERT INTO @p' + ParamIndex + N'
SELECT CONVERT(' + FilterColumns.ColumnSqlDataType + N', b.[value])
FROM OPENJSON(@JsonParams, ''$.Parameters'') AS a
CROSS APPLY OPENJSON(a.[value], ''$.Value'') AS b WHERE a.[key] = ' + ParamIndex + N';
'
-- Otherwise, declare the local variable as a regular variable.
ELSE
N' ' + FilterColumns.ColumnSqlDataType + N';
SELECT @p' + ParamIndex + N' = CONVERT(' + FilterColumns.ColumnSqlDataType + N', JSON_VALUE([value], ''$.Value'')) FROM OPENJSON(@JsonParams, ''$.Parameters'') WHERE [key] = ' + ParamIndex + N';
'
END
,
-- Parse the operand template by replacing the placeholders
@FilterString = @FilterString + N'
AND ' + REPLACE(
REPLACE(
FilterPredicates.PredicateTemplate
, '{Column}',FilterColumns.ColumnRealName)
, '{Parameter}', '@p' + ParamIndex)
FROM
(
-- This parses the XML into a relational structure
SELECT
ParamIndex = CONVERT(nvarchar(max), [key]) COLLATE database_default,
FilterColumnID = CONVERT(int, JSON_VALUE([value], '$.columnId')),
FilterPredicateID = CONVERT(int, JSON_VALUE([value], '$.operatorId'))
FROM
OPENJSON(@JsonParams, '$.Parameters')
) AS ParamValues
JOIN
FilterColumns
ON
ParamValues.FilterColumnID = FilterColumns.ColumnID
JOIN
FilterPredicates
ON
ParamValues.FilterPredicateID = FilterPredicates.PredicateID
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)'
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment