Sunday, May 08, 2011

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

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

Read more: Connect SQL