title: Execute dynamic query with IF-ELSE statements and sp_executesql command
body: <p>I have created the following table using the SQL Server</p>
  <pre class="lang-sql prettyprint-override"><code>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);
  </code></pre>
  <p>And I have also created a custom procedure using dynamic SQL to run a custom-oriented query</p>
  <p>Procedure:</p>
  <pre class="lang-sql prettyprint-override"><code>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 &lt;&gt; 0
                             THEN SET @SQL = @SQL+ ' AND TableName = @TableName'
                             ELSE  '''' + @all + ''' = ''' + @all + ''''
                       END
  
      SET @Field = CASE 
                      WHEN @Field IS NOT NULL AND @Field &lt;&gt; 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 = &gt;'
                          WHEN @Criterion = 'greater than or equal'
                             THEN SET @SQL = @SQL+ ' AND Criterion = &gt;='
                          WHEN @Criterion = 'less than'
                             THEN SET @SQL = @SQL+ ' AND Criterion = &lt;'
                          WHEN @Criterion = 'less than or equal'
                             THEN SET @SQL = @SQL+ ' AND Criterion = &lt;='
                          WHEN @Criterion = 'equal'
                             THEN SET @SQL = @SQL+ ' AND Criterion = ='
                          WHEN @Criterion = 'not equal'
                             THEN SET @SQL = @SQL+ ' AND Criterion = &lt;&gt;'
                          ELSE  '''' + @all + ''' = ''' + @all + ''''
                       END
  
      SET @Parameter = CASE 
                          WHEN @Parameter IS NOT NULL AND @Parameter &lt;&gt; 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
  </code></pre>
  <p>Briefly, I will explain what my procedure does:</p>
  <ul>
  <li><p>Step 1: Takes as input 4 parameters (<code>TableName, Field, Criterion, Parameter</code>)</p>
  </li>
  <li><p>Step 2: Sets a query to select all values based on a sample criterion (greater than, less than, etc)</p>
  </li>
  <li><p>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)</p>
  </li>
  <li><p>Step 4: I execute the command  <code>EXEC sp_executesql</code> to complete my query dynamically</p>
  </li>
  </ul>
  <p>When I try to create the procedure I get following errors:</p>
  <blockquote>
  <p>Msg 156, Level 15, State 1, Procedure usp_dynamicquery1, Line 45 [Batch Start Line 0]<br />
  Incorrect syntax near the keyword 'SET'.</p>
  <p>Msg 156, Level 15, State 1, Procedure usp_dynamicquery1, Line 46 [Batch Start  Line 0]<br />
  Incorrect syntax near the keyword 'ELSE'.</p>
  <p>Msg 156, Level 15, State 1, Procedure usp_dynamicquery1, Line 51 [Batch Start Line 0]<br />
  Incorrect syntax near the keyword 'SET'.</p>
  </blockquote>
  <p>Expected result when I run the query below:</p>
  <pre class="lang-sql prettyprint-override"><code>[usp_dynamicquery1] @TableName='test', @Field='number_of_products', @Criterion='greater than', @Parameter = '4'
  </code></pre>
  <p>Return the id_numbers with number_of_products &gt; 4 (thus the last three rows of the test table).</p>