sqlstored-procedureswhere-clausessms-16

tweaking stored proc


I have a stored proc that gets called with a parameter. I would like to check what the PARAM @Value is, and depending on the value, I want to add a part of a WHERE clause if it equals to a certain value.

Let's say the proc looks like this:

  Select Sum(Cost) as Cost, Car, Make
  INTO #TempTable1
  WHERE 
  1=1 and Date > '04/21/2020'

  IF @Value in (1,2,3)
  AND Color= 'Black'

  GROUP BY
  Car, Make

This is a much simpliefied code, my WHERE clause for @Value=1,2,3 will be much bigger.

So essentially, my goal is to add part of WHERE clause if the @Value = 1, 2, 3. Is something like doable?

EDIT: What if above my select statement i add something like

 DECLARE @WHERE AS VARCHAR(MAX)
 IF @VALUE IN (1,2,3) 
   SET @WHERE = 'color = ''black'''

Solution

  • You could add the condition :

    AND ((@VALUE IN (1,2,3) AND Color='Black') OR VALUE NOT IN (1,2,3))
    

    Or you could write a dynamic SQL.

    DECLARE @Statement NVARCHAR(1000);
    SET @Statement = 'Select Sum(Cost) as Cost, Car, Make INTO #TempTable1 WHERE   Date > ''04/21/2020'''
    IF(@value in (1,2,3))
      BEGIN
        SET @Statement=@Statement+' and Color=''Black'''
      END
    SET @Statement=@Statement+ ' GROUP BY Car,Make ' 
    EXEC sp_executesql   @Statement
    

    Alternative:

    Select Sum(Cost) as Cost, Car, Make
    INTO #TempTable1
    WHERE 
      1=1 and Date > '04/21/2020'
    AND  
        CASE WHEN @flag N (1,2,3) 
        THEN color
        ELSE 'Black'
        END = 'Black'
    GROUP BY
      Car, Make
    

    Last alternative:

    IF @value in (1,2,3) 
    BEGIN
        Select Sum(Cost) as Cost, Car, Make
        INTO #TempTable1
        WHERE 1=1 and Date > '04/21/2020'
        AND color='Black'
        GROUP BY Car, Make
    END
    ELSE
    BEGIN
        Select Sum(Cost) as Cost, Car, Make
        INTO #TempTable1
        WHERE 1=1 and Date > '04/21/2020'
        GROUP BY Car, Make
    END