Insert SQL WHERE clauses as parameter in a stored procedure and use only the non-null clauses and their parameter value

Question

My problem is a little bit complicated but I will try to simplify it. My basic idea is to declare 3 criteria (a criterion is a where clause) and 3 parameters (to use with the criteria). Those are declared for a stored procedure running with dynamic SQL.

What I want is to create a SELECT * FROM TableName and filter the table using only the pairs of only the non nullable criteria and parameter.

Dummy table:

CREATE TABLE test 
(
     id_number NVARCHAR(50) NOT NULL,
     number_of_products INT,
     username TEXT
);

INSERT INTO test (id_number, number_of_products, username)
VALUES (1000077004, 3, 'Jhon Smith'),
       (1000077005, 4, 'Nick Smith'),
       (1000077006, 4, 'Dale Smith'),
       (1000077007, 5, 'Diana Smith'),
       (1000077008, 5, 'Alice Smith'),
       (1000077009, 6, 'Antony Smith'),
       (1000077010, NULL, 'Bruce Smith');

SELECT * FROM test

For example if the user specifies only the (condition1=' >', parameter1='3') and (condition2=' <', parameter2='6') then the SQL query will be:

SELECT * FROM test
WHERE number_of_products condiction1 parameter1 AND condition2 paramater2 

But if the user specifies only the (condition1=' >', parameter1='5') then the SQL query will be:

SELECT * FROM test
WHERE number_of_products condiction1 parameter1

To accomplish this I have created the following stored procedure (it is incomplete):

CREATE OR ALTER PROCEDURE [dbo].[dynamicquery1] (
    @TableName NVARCHAR(50),
    @Field NVARCHAR(100) = NULL,
    @Criterion1 NVARCHAR(100) = NULL,
    @Parameter1 NVARCHAR(100) = NULL,
    @Criterion2 NVARCHAR(100) = NULL,
    @Parameter2 NVARCHAR(100) = NULL,
    @Criterion3 NVARCHAR(100) = NULL,
    @Parameter3 NVARCHAR(100) = NULL,
    @All VARCHAR(2) = '-1'
)
AS          
BEGIN
PRINT('Starting the procedure')

    SET NOCOUNT ON;  
 
    DECLARE 
        @SQL NVARCHAR(MAX),
        @SQL_WHERE NVARCHAR(MAX),
        @ParameterDef NVARCHAR(500);

    SET @ParameterDef = '@Parameter NVARCHAR(100)'
    SET @SQL = 'SELECT * FROM ' + @TableName;
    SET @SQL_WHERE = '';

    /* BUILD THE WHERE CLAUSE IF @Field IS PRESENT */

    IF NULLIF ( @Field, '' ) IS NOT NULL 
    
    BEGIN
        -- Field value
        SET @SQL_WHERE = ' WHERE ' + @Field;

        -- Set @Parameter value
        SET @Parameter1 =
            CASE WHEN NULLIF ( @Parameter1, '' ) IS NOT NULL
                    THEN @Parameter1
                 ELSE @All
            END;

        SET @Parameter2 =
            CASE WHEN NULLIF ( @Parameter2, '' ) IS NOT NULL
                    THEN @Parameter2
                 ELSE @All
            END;

        -- Field Comparison value
        IF @Field LIKE '%[0-9]%'
            PRINT('Column is numeric')
            BEGIN
                SET @SQL_WHERE += CASE @Criterion1
                    WHEN 'greater than' THEN ' >' + @Parameter1
                    WHEN 'greater than or equal' THEN ' >=' + @Parameter1
                    WHEN 'less than' THEN ' <' + @Parameter1
                    WHEN 'less than or equal' THEN ' <=' + @Parameter1
                    WHEN 'not equal' THEN ' <>' + @Parameter1
                    WHEN 'equal' THEN ' =' + @Parameter1
                    ELSE ''
                END;

                PRINT('Column is still numeric')
            END;

        IF @Field NOT LIKE '%[0-9]%'
            PRINT('Column is text')
            BEGIN
                SET @SQL_WHERE += CASE @Criterion1 
                WHEN 'start with' THEN ' LIKE ' + ''''+ @Parameter1 + '&'''
                WHEN 'end with' THEN ' LIKE ' + '''&' + @Parameter1 + ''''
                WHEN 'in any position' THEN ' LIKE ' + '''%' + @Parameter1 + '%'''
                WHEN 'in second position' THEN ' LIKE ' + '''_' + @Parameter1 + '%'''
                WHEN 'specific character and at least 2 characters in length' THEN ' LIKE ' + @Parameter1 + '_%'''
                WHEN 'specific character and at least 3 characters in length' THEN ' LIKE ' + @Parameter1 + '__%'''
                ELSE ''
            END;
        END;
    END;

    -- Finish SQL statement.
    SET @SQL = @SQL + ISNULL ( @SQL_WHERE, '' ) + ';';

    -- Execute the dynamic statement.
    PRINT(@SQL)
    PRINT(@ParameterDef)
    PRINT(@Parameter1)
    PRINT(@Criterion1)
    PRINT(@Parameter2)
    PRINT(@Criterion2)
    EXEC sp_executesql @SQL, @ParameterDef, @Parameter1=@Parameter1, @Parameter2=Parameter2;
    
END
GO

I would really appreciate your help on how to change the SET statement of Criterion1 and Parameter1 to include also the pairs of Criterion2, 3 and Parameters2, 3. But include them in the query only if they are not null so for example,

EXEC [dynamicquery1] @TableName='test', @Field='number_of_products', @Criterion1='greater than', @Parameter1 = '3', @Criterion2='less than or equal', @Parameter2 = '6'

The above EXEC will return the rows with number of products greater than 3 and less than or equal to 6.

Answer 1

As I have said in a comment, I really wouldn't recommend doing this, but if you are going to do it, there are a few things you need to change in your script. For example, you determine whether or not to use numeric operands:

IF @Field LIKE '%[0-9]%'
        PRINT('Column is numeric')

This relies on you putting a number in all of your numeric column names, and also not putting a number in any non-numeric, you would be much better off looking in the system catalogue views for the actual types:

SELECT  t.name
FROM    sys.columns AS c
        INNER JOIN sys.types AS t
            ON t.user_type_id = c.user_type_id
            AND t.system_type_id = c.system_type_id
WHERE   c.Name = @Field
AND     c.object_id = OBJECT_ID(@TableName, 'U');

This has the added benefit of also ensuring that the arguments @TableName and @Field are valid table and column names respectively, so provides some additional validation to your query.

If I was going to do this, I would create a table to store the list of operands that you want to use, along with a display name in a table:

CREATE TABLE dbo.Operands
(
        OperandID INT IDENTITY(1, 1) NOT NULL,
        Name VARCHAR(255) NOT NULL,
        SqlExpression VARCHAR(50) NOT NULL,
    CONSTRAINT PK_Operands__OperandID PRIMARY KEY (OperandID)
);
INSERT dbo.Operands(Name, SqlExpression)
VALUES 
    ('greater than' , ' > %s'),
    ('greater than or equal', ' >= %s'),
    ('less than', ' < %s');

You can then map this table to valid data types, this will also help to validate the arguments passed (e.g. if someone passes "Starts With" comparison but for a datetime column). In my demo I have skipped creating an actual table, and instead just used a table value constructor to create it, using the numeric and non-numeric values you have used in your question.

SELECT  *
FROM    (VALUES
            (1, 'greater than' , ' > {{parameter}}'),
            (1, 'greater than or equal', ' >= {{parameter}}'),
            (1, 'less than', ' < {{parameter}}'),
            (1, 'less than or equal', ' <= {{parameter}}'),
            (1, 'not equal', ' <> {{parameter}}'),
            (1, 'equal', ' = {{parameter}}'),
            (0, 'equal', ' = {{parameter}}'),
            (0, 'start with', ' LIKE CONCAT({{parameter}}, ''%'')'),
            (0, 'end with', ' LIKE CONCAT({{parameter}}, ''%'')'),
            (0, 'in any position', ' LIKE CONCAT(''%'', {{parameter}}, ''%'')'),
            (0, 'in second position', ' LIKE CONCAT(''_'', {{parameter}}, ''%'')'),
            (0, 'specific character and at least 2 characters in length', ' LIKE CONCAT({{parameter}}, ''_%'')'),
            (0, 'specific character and at least 3 characters in length', ' LIKE CONCAT({{parameter}}, ''__%'')')
        ) op (NumericField, Criterion, Operator);

As I say though, there is scope to improve this, but increasing the mapping complexity from simply numeric/non-numeric to account for other types.

The reason I am using {{parameter}} within the expression, is that later on I am going to replace it with the actual parameter name, and possibly an expression, so it is just a placeholder for now. A simple example would be:

SELECT  CONCAT(p.ColumnName, REPLACE(Operand, '{{parameter}}', p.ParameterName))
FROM    (VALUES
            (' = {{parameter}}', 'Column1', '@Parameter1'),
            (' >= {{parameter}}', 'Column2', '@Parameter2'),
            (' LIKE CONCAT(''%'', {{parameter}}, ''%'')', 'Column3', '@Parameter3')
        ) p (Operand, ColumnName, ParameterName);

Which returns

Column1 = @Parameter1
Column2 >= @Parameter2
Column3 LIKE CONCAT('%', @Parameter3, '%')

Within my demo I have just limited the types to ones that are text or numeric, as this appears to be what you were trying to do, so the following section will retrieve the actual type of the column passed in, and will set a field to signify whether it is numeric or not. This will be used in conjunction with operand table to determine if the field/criterion are a valid combination:

    DECLARE @IsNumericField BIT, @TypeName SYSNAME;
    SELECT  @IsNumericField = CASE WHEN t.name IN ('sysname', 'nvarchar', 'nchar', 'char', 'text', 'varchar', 'ntext') THEN 0 ELSE 1 END,
            @TypeName = t.name
    FROM    sys.columns AS c
            INNER JOIN sys.types AS t
                ON t.user_type_id = c.user_type_id
                AND t.system_type_id = c.system_type_id
    WHERE   c.Name = @Field
    AND     c.object_id = OBJECT_ID(@TableName, 'U')
    AND     t.name IN ('sysname', 'nvarchar', 'nchar', 'char', 'text', 'varchar', 'ntext', 'tinyint',
                     'smallint', 'int', 'real', 'money', 'float', 'decimal', 'numeric', 'smallmoney', 'bigint');

The next part is to bring this all together to actually build your where clause:

DECLARE @Criterion1 NVARCHAR(100) = 'greater than',
        @Parameter1 NVARCHAR(100) = '1',
        @Criterion2 NVARCHAR(100) = 'less than or equal',
        @Parameter2 NVARCHAR(100) = 5,
        @Criterion3 NVARCHAR(100) = NULL,
        @Parameter3 NVARCHAR(100) = NULL;

SELECT  CONCAT('AND ', 
            QUOTENAME(@Field), 
            REPLACE(op.Operator, '{{parameter}}', CONCAT('TRY_CONVERT(', @TypeName, ', ', p.ParameterName, ')')))
FROM    (VALUES
            (1, 'greater than' , ' > {{parameter}}'),
            (1, 'greater than or equal', ' >= {{parameter}}'),
            (1, 'less than', ' < {{parameter}}'),
            (1, 'less than or equal', ' <= {{parameter}}'),
            (1, 'not equal', ' <> {{parameter}}'),
            (1, 'equal', ' = {{parameter}}'),
            (0, 'equal', ' = {{parameter}}'),
            (0, 'start with', ' LIKE CONCAT({{parameter}}, ''%'')'),
            (0, 'end with', ' LIKE CONCAT({{parameter}}, ''%'')'),
            (0, 'in any position', ' LIKE CONCAT(''%'', {{parameter}}, ''%'')'),
            (0, 'in second position', ' LIKE CONCAT(''_'', {{parameter}}, ''%'')'),
            (0, 'specific character and at least 2 characters in length', ' LIKE CONCAT({{parameter}}, ''_%'')'),
            (0, 'specific character and at least 3 characters in length', ' LIKE CONCAT({{parameter}}, ''__%'')')
        ) op (NumericField, Criterion, Operator)
        INNER JOIN
        (VALUES
            (@Criterion1, @Parameter1, '@Parameter1'),
            (@Criterion2, @Parameter2, '@Parameter2'),
            (@Criterion3, @Parameter3, '@Parameter3')
        ) p (Criterion, ParameterValue, ParameterName)
            ON p.Criterion = op.Criterion
WHERE   op.NumericField = @IsNumericField;

As I have only passed values for parameter1 and parameter2, this returns two rows:

AND [number_of_products] > TRY_CONVERT(int, @Parameter1)
AND [number_of_products] <= TRY_CONVERT(int, @Parameter2)

I have used TRY_CONVERT() along with the typename retrieved earlier to and more gracefully handle invalid data, so if someone passes a parameter value of "String" for a numeric columnn, you won't get a conversion error, you will just get no results. If you want an error to be thrown, simply use CONVERT() instead.

Within the full working demo, I have used STRING_AGG() to concatenate this into a single variable.

Finally, when it comes to calling your SQL, you don't need to declare the parameters dynamically, if they don't appear in the SQL they won't be used, so declaring and passing all 3 won't be a problem:

    EXECUTE sp_executesql 
                @SQL, 
                N'@Parameter1 NVARCHAR(100), @Parameter2 NVARCHAR(100), @Parameter3 NVARCHAR(100)',
                @Parameter1,
                @Parameter2,
                @Parameter3;

Then all you need to do is bring all of this together into a stored procedure.

Example on DB<>Fiddle

CREATE OR ALTER PROCEDURE [dbo].[dynamicquery1] (
    @TableName sysname,
    @Field NVARCHAR(100) = NULL,
    @Criterion1 NVARCHAR(100) = NULL,
    @Parameter1 NVARCHAR(100) = NULL,
    @Criterion2 NVARCHAR(100) = NULL,
    @Parameter2 NVARCHAR(100) = NULL,
    @Criterion3 NVARCHAR(100) = NULL,
    @Parameter3 NVARCHAR(100) = NULL,
    @All VARCHAR(2) = '-1'
)
AS
BEGIN

    -- VALIDATE TABLE EXISTS
    IF NOT EXISTS (SELECT 1 FROM sys.tables AS t WHERE t.object_id = OBJECT_ID(@TableName))
    BEGIN
        RAISERROR ('Invalid table', 16, 1);
        RETURN;
    END

    DECLARE @SQL NVARCHAR(MAX) = CONCAT('SELECT * FROM ', @TableName);
    IF @Field IS NULL
    BEGIN
        EXECUTE sp_executesql @SQL;
        RETURN;
    END

    -- USE SYSTEM CATALOGUE VIEWS TO VALIDATE @FIELD AND GET THE CORRECT TYPE
    DECLARE @IsNumericField BIT, @TypeName SYSNAME;
    SELECT  @IsNumericField = CASE WHEN t.name IN ('sysname', 'nvarchar', 'nchar', 'char', 'text', 'varchar', 'ntext') THEN 0 ELSE 1 END,
            @TypeName = t.name
    FROM    sys.columns AS c
            INNER JOIN sys.types AS t
                ON t.user_type_id = c.user_type_id
                AND t.system_type_id = c.system_type_id
    WHERE   c.Name = @Field
    AND     c.object_id = OBJECT_ID(@TableName, 'U')
    AND     t.name IN ('sysname', 'nvarchar', 'nchar', 'char', 'text', 'varchar', 'ntext', 'tinyint',
                     'smallint', 'int', 'real', 'money', 'float', 'decimal', 'numeric', 'smallmoney', 'bigint');


    IF @IsNumericField IS NULL
    BEGIN
        -- If @Numeric field was not set it means the column doesn't exist, 
        -- or the type of the column is not numeric or text
        RAISERROR ('Invalid column or column is not queryable type', 16, 1);
        RETURN;
    END

    -- DECLARE THE WHERE CLAUSE FOR THE DYNAMIX SQL
    DECLARE @SQLWhere NVARCHAR(MAX) = ' WHERE 1 = 1 ';

    -- BUILD UP THE WHERE CLAUSE BASED ON THE CRITERIA AND PARAMETERS PASSED
    SELECT  @SQLWhere += STRING_AGG(CONCAT('AND ', 
                                    QUOTENAME(@Field), 
                                    REPLACE(op.Operator, '{{parameter}}', CONCAT('TRY_CONVERT(', @TypeName, ', ', p.ParameterName, ')'))),
                                 ' ')
    FROM    (VALUES
                (1, 'greater than' , ' > {{parameter}}'),
                (1, 'greater than or equal', ' >= {{parameter}}'),
                (1, 'less than', ' < {{parameter}}'),
                (1, 'less than or equal', ' <= {{parameter}}'),
                (1, 'not equal', ' <> {{parameter}}'),
                (1, 'equal', ' = {{parameter}}'),
                (0, 'equal', ' = {{parameter}}'),
                (0, 'start with', ' LIKE CONCAT({{parameter}}, ''%'')'),
                (0, 'end with', ' LIKE CONCAT({{parameter}}, ''%'')'),
                (0, 'in any position', ' LIKE CONCAT(''%'', {{parameter}}, ''%'')'),
                (0, 'in second position', ' LIKE CONCAT(''_'', {{parameter}}, ''%'')'),
                (0, 'specific character and at least 2 characters in length', ' LIKE CONCAT({{parameter}}, ''_%'')'),
                (0, 'specific character and at least 3 characters in length', ' LIKE CONCAT({{parameter}}, ''__%'')')
            ) op (NumericField, Criterion, Operator)
            INNER JOIN
            (VALUES
                (@Criterion1, @Parameter1, '@Parameter1'),
                (@Criterion2, @Parameter2, '@Parameter2'),
                (@Criterion3, @Parameter3, '@Parameter3')
            ) p (Criterion, ParameterValue, ParameterName)
                ON p.Criterion = op.Criterion
    WHERE   op.NumericField = @IsNumericField;

    SET @SQL += @SQLWhere;

    PRINT @SQL;
    EXECUTE sp_executesql 
                @SQL, 
                N'@Parameter1 NVARCHAR(100), @Parameter2 NVARCHAR(100), @Parameter3 NVARCHAR(100)',
                @Parameter1,
                @Parameter2,
                @Parameter3;
END

---

ADDENDUM

If you want to pass multiple fields, add new parameters (@Field1, @Field2 etc), then you adjust the table valued constructor to add a further column for your field name:

INNER JOIN
(VALUES
    (@Field1, @Criterion1, @Parameter1, '@Parameter1'),
    (@Field2, @Criterion2, @Parameter2, '@Parameter2'),
    (@Field3, @Criterion3, @Parameter3, '@Parameter3')
) p (ColumnName, Criterion, ParameterValue, ParameterName)
    ON p.Criterion = op.Criterion

And then use this new column to build your expressions rather than @Field:

SELECT  @SQLWhere += STRING_AGG(CONCAT('AND ', 
                                QUOTENAME(p.ColumnName),  
                                REPLACE(op.Operator, '
                                        {{parameter}}', 
                                        CONCAT('TRY_CONVERT(', t.Name, ', ', p.ParameterName, ')'))),
                             ' ')
                             
                             

Example on DB<>Fiddle