SQL Server: Conditional WHERE clause (Filter for a Filter)

No one is unaware of WHERE clause. Everyone knows that we can filter output records by using WHERE clause but recently I found my team member stuck when he need a Conditional WHERE clause (filter for a filter).  Let’s try to figure out the problem and its solution with a simple scenario.

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)
(Note: TSQL is a rich query language, a problem can be solved by several methods. Above query is just written to show conditional WHERE clause)