Dynamic filter in WHERE Clause with CASE_WHEN_THEN in SQL Server

 

I have very often seen that people are so used to with traditional methods and hence never think in terms of set based programming, they used to write code with evergreen traditional method which people used to use in sequential language like C or many be ASP etc.

 

Let us see this practically with an example.

 

Create one sample table with row given in following TSQL Script.

 


CREATE TABLE Employee

(

ID INT IDENTITY(1,1),

Name VARCHAR(10),

Dept VARCHAR(10)

)

GO

 

INSERT INTO Employee

SELECT 'Ritesh','DBA' UNION ALL

SELECT 'Rajan','Dev' UNION ALL

SELECT 'Bihag','Net'

GO

Now we have situation that if we pass “ALL” from application, it should show data from all department otherwise it should show data from the specific department which is provided in the variable.

Let us see method one where people used to use IF condition to check value and have two separate SELECT statement. I don’t prefer this one, why we should write two SELECT statement whereas we can write only single. If we have many conditions, we have to repeat SELECT query in this method which is not preferable from the maintenance point of view.

 


--Method 1:

DECLARE @Dept VARCHAR(10)

SET @Dept = 'ALL'

 

IF @Dept='ALL'

BEGIN

SELECT * FROM Employee

END

ELSE

BEGIN

SELECT * FROM Employee

WHERE Dept=@Dept

END

 

There are some people who uses method 2 where they take value in temporary table to find filter value and pass it to main SELECT clause so in this method you will have only one main SELECT clause but this is also less preferable method as it has multiple SELECT statement in set along with temporary table which affect performance adversary in most cases so don’t use temporary table unless there is no way around.

 


--Method 2:

DECLARE @Dept VARCHAR(10)

SET @Dept = 'ALL'

 

IF OBJECT_ID('tempdb..#DeptList') IS NOT NULL

DROP TABLE #DeptList

 

CREATE TABLE #DeptList (Dept VARCHAR(10))

 

IF @Dept='ALL'

BEGIN

INSERT INTO #DeptList (Dept)

SELECT Dept FROM Employee

END

ELSE

BEGIN

INSERT INTO #DeptList (Dept)

SELECT Dept FROM Employee WHERE Dept=@Dept

END

 

SELECT * FROM Employee WHERE Dept IN (SELECT Dept From #DeptList)

GO

 

Now let us see method 3 which is my preferable & shortest method in this kind of situation which has good performance as well when compare it with above two method.

 


--Method 3

DECLARE @Dept VARCHAR(10)

SET @Dept = 'ALL'

 

SELECT * FROM Employee

WHERE Dept=CASE WHEN @Dept='ALL' THEN Dept ELSE @Dept END

 

Are you using any other method to achieve the task explained here?

 

It is interesting to discuss…!!!!

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.