>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)
Advertisements

Posted on May 6, 2011, in Sql Server 2005, Sql Server 2008, TSQL Tips n Tricks. Bookmark the permalink. 4 Comments.

  1. >SELECT * FROM #ResultWHERE (@IdType='Student' AND StudentId = @Id) OR (@IdType='Teacher' AND TeacherId = @Id) OR (@IdType='Group' AND GroupId = @Id)

  2. >Nice writeup. I would like to add this SQL where clause tutorial.

  3. thank you very much… this help me…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: