I have a table 'Route' with Id ,Name and Active columns. If Id = 0 I want to select all the Id's where Active = true.
If Id > 0 I want to select the data for particular Id where Active = true.
I tried with following Query. But not working. (here I passed 2 for a Id value.) Can someone support me?
DECLARE @ID INT
SET @ID = 2
SELECT DISTINCT Id ,Name
FROM Route
WHERE
CASE WHEN @ID > 0
THEN Id = @ID AND Active= 1
ELSE
Active=1
END
Sample Data Attached.
No need to use case can be done using where clause only.
DECLARE @ID INT
SET @ID = 2
SELECT DISTINCT Id ,Name
FROM Route
WHERE Active=1 AND (Id = @Id OR @Id = 0);
Explanation:
WHERE
CASE WHEN @ID > 0
THEN Id = @ID AND Active= 1
ELSE
Active=1
a. if @ID > 0 then Id = @ID AND Active= 1
b. else Active=1
If you look into both cases closely, Active= 1 remains same in both cases so it can be taken safely to outside of the condition
Then the remaining part is, if @ID > 0 then Id = @ID i.e. select the row as per the parameter value when parameter has valid value, otherwise select all.
So, if you combine both it becomes
WHERE Active=1 AND (Id = @Id OR @Id = 0);
