We had a situation in which we had to use case in where condition. This post helps in understanding “How to use CASE in Where Condition”. Lets consider a situation where we need to write a query which should satisfy the below conditions
1. We will pass a parameter: Department Name
2. If we pass the department name, it should return that dept details
3. If we pass “ALL” as perm value it should return all department details
4. Of course a lot ways to implement this but here intension is how we can use CASE in Where statement
SELECT DeptCode, Name, Location, Campus
FROM Dept
WHERE
(
CASE WHEN @Parameter <> ‘ALL’ AND Name= @Parameter THEN 1
WHEN @Parameter= ‘ALL’ THEN 1
END
) = 1
DECLARE @Parameter VARCHAR(100)=’ALL’
SELECT DeptCode, Name, Location, Campus
FROM Dept
WHERE
(
CASE
WHEN @Parameter <> ‘ALL’ AND Name= @Parameter THEN 1
WHEN @Parameter= ‘ALL’ THEN 1
END
) = 1
