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 

/*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.

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

3 thoughts on “Dynamic filter in WHERE Clause with CASE_WHEN_THEN in SQL Server”

  1. Yes, agreed with you that method 3 is better option .
    One another method is dynamic sql using sp_executesql to achieve same. What’s your opinion about same in terms of optimization ?

    1. Viral,

      SP_ExecuteSQL is also one of the option as we can create dynamic SQL and execute it. For the performance point of view, I wouldn’t like to go for it as long as possible as we can’t get complete benefit of caching if we have dynamic SQL.

  2. there is another way you can use it

    DECLARE @Name VARCHAR(50)
    SET @Name = ‘ALL’
    SELECT TOP 1000
    [Id] ,
    [Name] ,
    [Age] ,
    [Salary]
    FROM [Emp]
    WHERE ( Name = @Name )
    OR ( @Name = ‘ALL’ )

Comments are closed.