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