tweaking stored proc

Question

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'''

Answer 1

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