For #tsql2sday, sometimes the best dynamic SQL code you can write is the code that was dynamically generated for you from your tables or views.
The dynamic SQL stored procedure named dbo.PersonSearch below was generated with the free open-source stored procedure named sp_CRUDgen. Find the YouTube playlist here with all the sp_CRUDGen videos here, visit the GitHub project here, view my blog posts here.
Executing this stored procedure:
EXEC dbo.sp_CRUDGen @SchemaTableOrViewName = N'dbo.Person';
CREATE PROCEDURE dbo.PersonSearch (
@PersonIdValue nvarchar(MAX) = NULL
,@PersonIdOperator nvarchar(30) = N'Equals'
,@FirstNameValue nvarchar(MAX) = NULL
,@FirstNameOperator nvarchar(30) = N'Equals'
,@LastNameValue nvarchar(MAX) = NULL
,@LastNameOperator nvarchar(30) = N'Equals'
,@AtTimeZoneName nvarchar(MAX) = N'Central Standard Time' /* SELECT name FROM sys.time_zone_info */
,@PageNumber int = 1
,@PageSize int = 100
,@OrderColumns nvarchar(MAX) = N'[{"SchemaName": "dbo", "TableName": "Person", "ColumnName": "PersonId", "Direction": "ASC", "OrderNumber": 1}]'
,@Debug bit = 0
)
AS
BEGIN
SET NOCOUNT, XACT_ABORT ON;
/* Stored Procedure Variables */
DECLARE @StringToExecute nvarchar(MAX);
DECLARE @SeparatorStartingPosition bigint;
DECLARE @ParameterDefinition nvarchar(MAX);
DECLARE @OrderByString nvarchar(MAX);
SET @StringToExecute = N'';
SET @SeparatorStartingPosition = 0;
SET @OrderByString = N'';
/* Parameter Variables - This is for splitting parameters into begin and end variables for BETWEEN operations */
DECLARE @PersonIdBegin int;
DECLARE @PersonIdEnd int;
DECLARE @FirstNameBegin nvarchar(100);
DECLARE @FirstNameEnd nvarchar(100);
DECLARE @LastNameBegin nvarchar(100);
DECLARE @LastNameEnd nvarchar(100);
/* Create Temp Tables - This is for inserting JSON into for passing a list of parameter values */
CREATE TABLE #PersonIdValue ([PersonId] int NULL);
CREATE TABLE #FirstNameValue ([FirstName] nvarchar(100) NULL);
CREATE TABLE #LastNameValue ([LastName] nvarchar(100) NULL);
/* Create Temp Table - This is to store the table aliases for translation */
CREATE TABLE #TableAliasList (
SchemaName nvarchar(128) NULL
,TableName nvarchar(128) NULL
,TableAlias nvarchar(128) NULL
);
INSERT INTO #TableAliasList (SchemaName, TableName, TableAlias)
VALUES
(N'dbo', N'Person', N'P');
/* Create Temp Tables - This is for inserting JSON into for passing a list of parameter values */
CREATE TABLE #OrderColumns (
SchemaName nvarchar(128) NULL
,TableName nvarchar(128) NULL
,ColumnName nvarchar(128) NOT NULL
,Direction nvarchar(4) NULL
,OrderNumber int NULL
);
/* Extract JSON into temporary table for use with ordering results */
INSERT INTO #OrderColumns (SchemaName, TableName, ColumnName, Direction, OrderNumber)
SELECT
SchemaName = CASE WHEN LEN(OC.SchemaName) > 0 THEN OC.SchemaName ELSE NULL END
,TableName = CASE WHEN LEN(OC.TableName) > 0 THEN OC.TableName ELSE NULL END
,ColumnName = CASE WHEN LEN(OC.ColumnName) > 0 THEN OC.ColumnName ELSE NULL END
,Direction = CASE WHEN LEN(OC.Direction) > 0 THEN OC.Direction ELSE NULL END
,OrderNumber = OC.OrderNumber
FROM
OPENJSON(@OrderColumns)
WITH (
SchemaName nvarchar(128)
,TableName nvarchar(128)
,ColumnName nvarchar(128)
,Direction nvarchar(4)
,OrderNumber int
) AS OC;
/* Check if OrderColumn parameters are valid for ORDER BY */
IF EXISTS (SELECT * FROM #OrderColumns WHERE ColumnName NOT IN (
N'PersonId', N'FirstName', N'LastName'
)
)
BEGIN
;THROW 52001, 'OrderColumn parameter is not a valid table column!', 1;
/* Use RAISERROR below if you do not need a hard error thrown.
RAISERROR(N'OrderColumn parameter is not a valid table column!', 1, 1) WITH NOWAIT;
RETURN -1;*/
END;
/* Build the ORDER BY */
SELECT
@OrderByString = @OrderByString + N', ' + CASE WHEN TAL.TableAlias IS NOT NULL THEN QUOTENAME(TAL.TableAlias) + N'.' ELSE N'' END + QUOTENAME(OC.ColumnName) + CASE WHEN OC.Direction = N'DESC' THEN N' DESC' ELSE N' ASC' END
FROM
#OrderColumns AS OC
LEFT OUTER JOIN #TableAliasList AS TAL
ON CASE WHEN OC.SchemaName IS NOT NULL
THEN OC.SchemaName
ELSE (
SELECT TOP (1)
TAL.SchemaName
FROM
#TableAliasList AS TAL
WHERE
TAL.TableName = OC.TableName
ORDER BY
TAL.SchemaName
) END = TAL.SchemaName
AND OC.TableName = TAL.TableName
ORDER BY
OC.OrderNumber ASC;
/* Fix the first item */
IF LEN(@OrderByString) > 0
BEGIN
SET @OrderByString = RIGHT(@OrderByString, LEN(@OrderByString) - 2);
END;
SET @StringToExecute = N'
/* Executed by stored procedure named [dbo].[PersonSearch] */' + N'
WITH Keys
AS (
SELECT DISTINCT
[PersonId] = [P].[PersonId]
,RowNumber = DENSE_RANK() OVER (ORDER BY ' + @OrderByString + N')
FROM
[dbo].[Person] AS [P]
WHERE
1 = 1'
IF @PersonIdValue IS NOT NULL OR @PersonIdOperator IN ('Blanks', 'NonBlanks')
BEGIN
/* Figure out if the value contains the string [ to ] */
SET @SeparatorStartingPosition = CHARINDEX(' to ', @PersonIdValue);
IF @PersonIdOperator IN ('Exists', 'NotExists')
BEGIN
/* Extract JSON array into temporary table for use with Exists and NotExists where operators */
INSERT INTO #PersonIdValue ([PersonId])
SELECT CAST(Value AS int) FROM OPENJSON(@PersonIdValue);
END
ELSE
BEGIN
/* Split the value into begin and end variables for other where operators */
IF @SeparatorStartingPosition > 0 AND @PersonIdOperator IN ('Between', 'BetweenWithBlanks', 'NotBetween', 'NotBetweenWithBlanks')
BEGIN
SET @PersonIdBegin = SUBSTRING(@PersonIdValue, 1, @SeparatorStartingPosition - 1);
SET @PersonIdEnd = SUBSTRING(@PersonIdValue, @SeparatorStartingPosition + 4, LEN(@PersonIdValue));
END;
ELSE
BEGIN
SET @PersonIdBegin = @PersonIdValue;
SET @PersonIdEnd = @PersonIdValue;
END;
END;
SET @StringToExecute = @StringToExecute +
CASE @PersonIdOperator
WHEN 'Equals' THEN N'
AND [P].[PersonId] = @PersonIdBegin'
WHEN 'EqualsWithBlanks' THEN N'
AND ([P].[PersonId] = @PersonIdBegin OR [P].[PersonId] IS NULL)'
WHEN 'DoesNotEqual' THEN N'
AND [P].[PersonId] <> @PersonIdBegin'
WHEN 'DoesNotEqualWithBlanks' THEN N'
AND ([P].[PersonId] <> @PersonIdBegin OR [P].[PersonId] IS NULL)'
WHEN 'GreaterThan' THEN N'
AND [P].[PersonId] > @PersonIdBegin'
WHEN 'GreaterThanWithBlanks' THEN N'
AND ([P].[PersonId] > @PersonIdBegin OR [P].[PersonId] IS NULL)'
WHEN 'GreaterThanOrEqualTo' THEN N'
AND [P].[PersonId] >= @PersonIdBegin'
WHEN 'GreaterThanOrEqualToWithBlanks' THEN N'
AND ([P].[PersonId] >= @PersonIdBegin OR [P].[PersonId] IS NULL)'
WHEN 'LessThan' THEN N'
AND [P].[PersonId] < @PersonIdBegin'
WHEN 'LessThanWithBlanks' THEN N'
AND ([P].[PersonId] < @PersonIdBegin OR [P].[PersonId] IS NULL)'
WHEN 'LessThanOrEqualTo' THEN N'
AND [P].[PersonId] <= @PersonIdBegin'
WHEN 'LessThanOrEqualToWithBlanks' THEN N'
AND ([P].[PersonId] <= @PersonIdBegin OR [P].[PersonId] IS NULL)' WHEN 'Between' THEN N' AND [P].[PersonId] BETWEEN @PersonIdBegin AND @PersonIdEnd' WHEN 'BetweenWithBlanks' THEN N' AND ([P].[PersonId] BETWEEN @PersonIdBegin AND @PersonIdEnd OR [P].[PersonId] IS NULL)' WHEN 'NotBetween' THEN N' AND [P].[PersonId] NOT BETWEEN @PersonIdBegin AND @PersonIdEnd' WHEN 'NotBetweenWithBlanks' THEN N' AND ([P].[PersonId] NOT BETWEEN @PersonIdBegin AND @PersonIdEnd OR [P].[PersonId] IS NULL)' WHEN 'Blanks' THEN N' AND [P].[PersonId] IS NULL' WHEN 'NonBlanks' THEN N' AND [P].[PersonId] IS NOT NULL' WHEN 'Exists' THEN N' AND EXISTS (SELECT * FROM #PersonIdValue WHERE [PersonId] = [P].[PersonId])' WHEN 'NotExists' THEN N' AND NOT EXISTS (SELECT * FROM #PersonIdValue WHERE [PersonId] = [P].[PersonId])' ELSE N' AND 1 = 2 /* Operator is not supported */' END END IF @FirstNameValue IS NOT NULL OR @FirstNameOperator IN ('Blanks', 'NonBlanks') BEGIN /* Figure out if the value contains the string [ to ] */ SET @SeparatorStartingPosition = CHARINDEX(' to ', @FirstNameValue); IF @FirstNameOperator IN ('Exists', 'NotExists') BEGIN /* Extract JSON array into temporary table for use with Exists and NotExists where operators */ INSERT INTO #FirstNameValue ([FirstName]) SELECT CAST(Value AS nvarchar(100)) FROM OPENJSON(@FirstNameValue); END ELSE BEGIN /* Split the value into begin and end variables for other where operators */ IF @SeparatorStartingPosition > 0 AND @FirstNameOperator IN ('Between', 'BetweenWithBlanks', 'NotBetween', 'NotBetweenWithBlanks')
BEGIN
SET @FirstNameBegin = SUBSTRING(@FirstNameValue, 1, @SeparatorStartingPosition - 1);
SET @FirstNameEnd = SUBSTRING(@FirstNameValue, @SeparatorStartingPosition + 4, LEN(@FirstNameValue));
END;
ELSE
BEGIN
SET @FirstNameBegin = @FirstNameValue;
SET @FirstNameEnd = @FirstNameValue;
END;
END;
SET @StringToExecute = @StringToExecute +
CASE @FirstNameOperator
WHEN 'Equals' THEN N'
AND [P].[FirstName] = '''' + @FirstNameBegin + '''''
WHEN 'EqualsWithBlanks' THEN N'
AND ([P].[FirstName] = '''' + @FirstNameBegin + '''' OR [P].[FirstName] IS NULL OR [P].[FirstName] = '''')'
WHEN 'DoesNotEqual' THEN N'
AND [P].[FirstName] <> '''' + @FirstNameBegin + '''''
WHEN 'DoesNotEqualWithBlanks' THEN N'
AND ([P].[FirstName] <> '''' + @FirstNameBegin + '''' OR [P].[FirstName] IS NULL OR [P].[FirstName] = '''')'
WHEN 'BeginsWith' THEN N'
AND [P].[FirstName] LIKE '''' + @FirstNameBegin + ''%'''
WHEN 'BeginsWithWithBlanks' THEN N'
AND ([P].[FirstName] LIKE '''' + @FirstNameBegin + ''%'' OR [P].[FirstName] IS NULL OR [P].[FirstName] = '''')'
WHEN 'EndsWith' THEN N'
AND [P].[FirstName] LIKE ''%'' + @FirstNameBegin + '''''
WHEN 'EndsWithWithBlanks' THEN N'
AND ([P].[FirstName] LIKE ''%'' + @FirstNameBegin + '''' OR [P].[FirstName] IS NULL OR [P].[FirstName] = '''')'
WHEN 'Contains' THEN N'
AND [P].[FirstName] LIKE ''%'' + @FirstNameBegin + ''%'''
WHEN 'ContainsWithBlanks' THEN N'
AND ([P].[FirstName] LIKE ''%'' + @FirstNameBegin + ''%'' OR [P].[FirstName] IS NULL OR [P].[FirstName] = '''')'
WHEN 'DoesNotContain' THEN N'
AND [P].[FirstName] NOT LIKE ''%'' + @FirstNameBegin + ''%'''
WHEN 'DoesNotContainWithBlanks' THEN N'
AND ([P].[FirstName] NOT LIKE ''%'' + @FirstNameBegin + ''%'' OR [P].[FirstName] IS NULL OR [P].[FirstName] = '''')'
WHEN 'Blanks' THEN N'
AND ([P].[FirstName] IS NULL OR [P].[FirstName] = '''')'
WHEN 'NonBlanks' THEN N'
AND (NULLIF(LEN([P].[FirstName]), 0)) IS NOT NULL'
WHEN 'Exists' THEN N'
AND EXISTS (SELECT * FROM #FirstNameValue WHERE [FirstName] = [P].[FirstName])'
WHEN 'NotExists' THEN N'
AND NOT EXISTS (SELECT * FROM #FirstNameValue WHERE [FirstName] = [P].[FirstName])'
ELSE N'
AND 1 = 2 /* Operator is not supported */'
END
END
IF @LastNameValue IS NOT NULL OR @LastNameOperator IN ('Blanks', 'NonBlanks')
BEGIN
/* Figure out if the value contains the string [ to ] */
SET @SeparatorStartingPosition = CHARINDEX(' to ', @LastNameValue);
IF @LastNameOperator IN ('Exists', 'NotExists')
BEGIN
/* Extract JSON array into temporary table for use with Exists and NotExists where operators */
INSERT INTO #LastNameValue ([LastName])
SELECT CAST(Value AS nvarchar(100)) FROM OPENJSON(@LastNameValue);
END
ELSE
BEGIN
/* Split the value into begin and end variables for other where operators */
IF @SeparatorStartingPosition > 0 AND @LastNameOperator IN ('Between', 'BetweenWithBlanks', 'NotBetween', 'NotBetweenWithBlanks')
BEGIN
SET @LastNameBegin = SUBSTRING(@LastNameValue, 1, @SeparatorStartingPosition - 1);
SET @LastNameEnd = SUBSTRING(@LastNameValue, @SeparatorStartingPosition + 4, LEN(@LastNameValue));
END;
ELSE
BEGIN
SET @LastNameBegin = @LastNameValue;
SET @LastNameEnd = @LastNameValue;
END;
END;
SET @StringToExecute = @StringToExecute +
CASE @LastNameOperator
WHEN 'Equals' THEN N'
AND [P].[LastName] = '''' + @LastNameBegin + '''''
WHEN 'EqualsWithBlanks' THEN N'
AND ([P].[LastName] = '''' + @LastNameBegin + '''' OR [P].[LastName] IS NULL OR [P].[LastName] = '''')'
WHEN 'DoesNotEqual' THEN N'
AND [P].[LastName] <> '''' + @LastNameBegin + '''''
WHEN 'DoesNotEqualWithBlanks' THEN N'
AND ([P].[LastName] <> '''' + @LastNameBegin + '''' OR [P].[LastName] IS NULL OR [P].[LastName] = '''')'
WHEN 'BeginsWith' THEN N'
AND [P].[LastName] LIKE '''' + @LastNameBegin + ''%'''
WHEN 'BeginsWithWithBlanks' THEN N'
AND ([P].[LastName] LIKE '''' + @LastNameBegin + ''%'' OR [P].[LastName] IS NULL OR [P].[LastName] = '''')'
WHEN 'EndsWith' THEN N'
AND [P].[LastName] LIKE ''%'' + @LastNameBegin + '''''
WHEN 'EndsWithWithBlanks' THEN N'
AND ([P].[LastName] LIKE ''%'' + @LastNameBegin + '''' OR [P].[LastName] IS NULL OR [P].[LastName] = '''')'
WHEN 'Contains' THEN N'
AND [P].[LastName] LIKE ''%'' + @LastNameBegin + ''%'''
WHEN 'ContainsWithBlanks' THEN N'
AND ([P].[LastName] LIKE ''%'' + @LastNameBegin + ''%'' OR [P].[LastName] IS NULL OR [P].[LastName] = '''')'
WHEN 'DoesNotContain' THEN N'
AND [P].[LastName] NOT LIKE ''%'' + @LastNameBegin + ''%'''
WHEN 'DoesNotContainWithBlanks' THEN N'
AND ([P].[LastName] NOT LIKE ''%'' + @LastNameBegin + ''%'' OR [P].[LastName] IS NULL OR [P].[LastName] = '''')'
WHEN 'Blanks' THEN N'
AND ([P].[LastName] IS NULL OR [P].[LastName] = '''')'
WHEN 'NonBlanks' THEN N'
AND (NULLIF(LEN([P].[LastName]), 0)) IS NOT NULL'
WHEN 'Exists' THEN N'
AND EXISTS (SELECT * FROM #LastNameValue WHERE [LastName] = [P].[LastName])'
WHEN 'NotExists' THEN N'
AND NOT EXISTS (SELECT * FROM #LastNameValue WHERE [LastName] = [P].[LastName])'
ELSE N'
AND 1 = 2 /* Operator is not supported */'
END
END
SET @StringToExecute = @StringToExecute + N'
),Counts
AS (
SELECT RowsTotal = COUNT_BIG(*) FROM Keys AS [K]
)
SELECT
[PersonId] = [P].[PersonId]
,[FirstName] = [P].[FirstName]
,[LastName] = [P].[LastName]
,RowsTotal = Counts.RowsTotal
FROM
Keys
INNER JOIN [dbo].[Person] AS [P]
ON [Keys].[PersonId] = [P].[PersonId]
CROSS JOIN Counts
WHERE
[Keys].RowNumber > ((CAST(@PageNumber AS bigint) -1 ) * CAST(@PageSize AS bigint))
AND [Keys].RowNumber < ((CAST(@PageNumber AS bigint) * CAST(@PageSize AS bigint) ) + 1)
ORDER BY
[Keys].RowNumber;'
IF @Debug = 1
BEGIN
SELECT
CAST(N'/* Click here to view the generated code.
Copy just the T-SQL below this block comment into a new query window to execute. */
' AS nvarchar(MAX)) + @StringToExecute + N'
/* Copy just the T-SQL above this block comment into a new query window to execute. */
' AS [processing-instruction(output)] FOR XML PATH('')
END;
ELSE
BEGIN
SET @ParameterDefinition = N'@PageNumber int
,@PageSize int
,@AtTimeZoneName nvarchar(max)
,@PersonIdBegin int
,@PersonIdEnd int
,@FirstNameBegin nvarchar(100)
,@FirstNameEnd nvarchar(100)
,@LastNameBegin nvarchar(100)
,@LastNameEnd nvarchar(100)';
EXEC sys.sp_executesql
@stmt = @StringToExecute
,@params = @ParameterDefinition
,@PageNumber = @PageNumber
,@PageSize = @PageSize
,@AtTimeZoneName = @AtTimeZoneName
,@PersonIdBegin = @PersonIdBegin
,@PersonIdEnd = @PersonIdEnd
,@FirstNameBegin = @FirstNameBegin
,@FirstNameEnd = @FirstNameEnd
,@LastNameBegin = @LastNameBegin
,@LastNameEnd = @LastNameEnd;
END
END;
