Execute dynamic query with IF-ELSE statements and sp_executesql command

Question

I have created the following table using the SQL Server

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

INSERT INTO test (id_number, number_of_products)
VALUES (1000077004, 3),
       (1000077005, 4),
       (1000077006, 4),
       (1000077007, 5),
       (1000077008, 5),
       (1000077009, 6);

And I have also created a custom procedure using dynamic SQL to run a custom-oriented query

Procedure:

CREATE PROCEDURE [dbo].[usp_dynamicquery1]
    (@TableName NVARCHAR(50) = NULL,
     @Field     NVARCHAR(100) = NULL,
     @Criterion NVARCHAR(100) = NULL,
     @Parameter NVARCHAR(100) = NULL)
AS          
BEGIN
    SET NOCOUNT ON;  
 
    DECLARE @SQL          NVARCHAR(MAX)
    DECLARE @ParameterDef NVARCHAR(500)
    DECLARE @all          VARCHAR(2) = '-1'

    SET @ParameterDef = '@TableName NVARCHAR(50),
                         @Field     NVARCHAR(100),
                         @Criterion NVARCHAR(100),
                         @Parameter  NVARCHAR(100)'

    SET @SQL = 'SELECT *
                FROM TableName
                WHERE Field Criterion Parameter'

    SET @TableName = CASE 
                        WHEN @TableName IS NOT NULL AND @TableName <> 0
                           THEN SET @SQL = @SQL+ ' AND TableName = @TableName'
                           ELSE  '''' + @all + ''' = ''' + @all + ''''
                     END

    SET @Field = CASE 
                    WHEN @Field IS NOT NULL AND @Field <> 0
                       THEN SET @SQL = @SQL+ ' AND Field = @Field'
                       ELSE  '''' + @all + ''' = ''' + @all + ''''
                 END

    SET @Criterion = CASE 
                        WHEN @Criterion = 'greater than'
                           THEN SET @SQL = @SQL+ ' AND Criterion = >'
                        WHEN @Criterion = 'greater than or equal'
                           THEN SET @SQL = @SQL+ ' AND Criterion = >='
                        WHEN @Criterion = 'less than'
                           THEN SET @SQL = @SQL+ ' AND Criterion = <'
                        WHEN @Criterion = 'less than or equal'
                           THEN SET @SQL = @SQL+ ' AND Criterion = <='
                        WHEN @Criterion = 'equal'
                           THEN SET @SQL = @SQL+ ' AND Criterion = ='
                        WHEN @Criterion = 'not equal'
                           THEN SET @SQL = @SQL+ ' AND Criterion = <>'
                        ELSE  '''' + @all + ''' = ''' + @all + ''''
                     END

    SET @Parameter = CASE 
                        WHEN @Parameter IS NOT NULL AND @Parameter <> 0
                           THEN SET @SQL = @SQL+ ' AND Parameter = @Parameter'
                           ELSE  '''' + @all + ''' = ''' + @all + ''''
                     END

    EXEC sp_executesql @SQL,  
              @ParameterDef, 
              @TableName = @TableName, @Field = @Field, @Criterion = @Criterion, @Parameter = @Parameter
END

Briefly, I will explain what my procedure does:

• Step 1: Takes as input 4 parameters (TableName, Field, Criterion, Parameter)

• Step 2: Sets a query to select all values based on a sample criterion (greater than, less than, etc)

• Step 3: Runs 4 if-else statements to define the value of each variable. Basically, if the user gives a value then the parameters takes that value, else the parameter takes the value '-1' (@all)

• Step 4: I execute the command EXEC sp_executesql to complete my query dynamically

When I try to create the procedure I get following errors:

Msg 156, Level 15, State 1, Procedure usp_dynamicquery1, Line 45 [Batch Start Line 0]
Incorrect syntax near the keyword 'SET'.

Msg 156, Level 15, State 1, Procedure usp_dynamicquery1, Line 46 [Batch Start Line 0]
Incorrect syntax near the keyword 'ELSE'.

Msg 156, Level 15, State 1, Procedure usp_dynamicquery1, Line 51 [Batch Start Line 0]
Incorrect syntax near the keyword 'SET'.

Expected result when I run the query below:

[usp_dynamicquery1] @TableName='test', @Field='number_of_products', @Criterion='greater than', @Parameter = '4'

Return the id_numbers with number_of_products > 4 (thus the last three rows of the test table).

Answer 1

You have the right idea and are headed in the right direction. I figured I'd give you a hand to help you learn how I would approach this.

Here is my version of your stored procedure:

CREATE OR ALTER PROCEDURE [dbo].[usp_dynamicquery1] (
    @TableName NVARCHAR(50),
    @Field NVARCHAR(100) = NULL,
    @Criterion NVARCHAR(100) = NULL,
    @Parameter NVARCHAR(100) = NULL,
    @All VARCHAR(2) = '-1'
)
AS          
BEGIN

    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.
        SET @SQL_WHERE = ' WHERE ' + @Field;

        -- Field Comparison.
        SET @SQL_WHERE += CASE @Criterion 
            WHEN 'greater than' THEN ' >'
            WHEN 'greater than or equal' THEN ' >='
            WHEN 'less than' THEN ' <'
            WHEN 'less than or equal' THEN ' <='
            WHEN 'not equal' THEN ' <>'
            ELSE ' ='
        END;

        -- Field Parameter.
        SET @SQL_WHERE += ' @Parameter';

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

    END

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

    -- Execute the dynamic statement.
    EXEC sp_executesql @SQL, @ParameterDef, @Parameter = @Parameter;

END
GO

A few quick things worth noting:

• @TableName must be required, otherwise, what do you intend to query?

• The WHERE clause is only constructed if @Field is present.

• Shortened the @ParameterDef variable to include @Parameter only. The rest of the t-sql is formed prior to calling sp_execute_sql. I left @Parameter so that you wouldn't have to worry about single ticks around values, etc.

• Added @All as a DEFAULTed parameter (-1) to the SP to the value can be changed if wanted.