Adding case statement in where clause sometime we may not get the exact output we required. some solution suggested to add or condition instead of using case statement which again not giving desired results. but one solution I have found using case statement in where clause in reverse way.
Suppose we have a table to keep students result with follow structure
CREATE TABLE #Result
INSERT INTO #Result
SELECT 101,1,1,'PASS',510 UNION ALL
SELECT 102,1,1,'PASS',622 UNION ALL
SELECT 103,2,1,'FAIL',174 UNION ALL
SELECT 104,2,2,'PASS',652 UNION ALL
Our requirement is to create a stored procedure with only two parameters, one for id (it could be student, teacher or group id), we will call it @id and other to hold information that will decide that what type of id is being passed to stored procedure i.e. student, teacher or group, we will call it @idType
DECLARE @Id INT -- It could be StudentId,TeacherId,GroupId
DECLARE @IdType VARCHAR(10) -- Type could be Student,Teacher or Group
We need a query which can be used for all three criteria
SELECT @Id = 2, @IdType= 'Teacher'
--OR-- @Id = 102, @IdType= 'Student'
--OR-- @Id = 1, @IdType= 'Group'
Let’s move to our targeted query, with conditional where clause.
SELECT * FROM #Result
WHERE 1 =(CASE
WHEN @IdType='Student' AND StudentId = @Id
WHEN @IdType='Teacher' AND TeacherId = @Id
WHEN @IdType='Group' AND GroupId = @Id
ELSE 0 END)