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

    (

      StudentId INT,

      TeacherId INT,

      GroupId INT,

      Result VARCHAR(10),

      MarksObtained INT

    )

GO

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

SELECT 105,3,2,'FAIL',134

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

                  THEN 1

            WHEN @IdType='Teacher' AND TeacherId = @Id

                  THEN 1

            WHEN @IdType='Group' AND GroupId = @Id

                  THEN 1

            ELSE 0 END)