Category: CASE WHEN THEN

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.