How to Write Case Statement in WHERE Clause? – Interview Question of the Week #127

Question: How to Write Case Statement in WHERE Clause?

Answer: This is a very popular question. This question usually comes up in the context of writing search condition where the user is not sure if there will be condition or not.

First of all, as Kalen Dealaney mentioned (Thank you!) on the twitter Case is not a statement it is an expression.

Recently, during my SQL Server Performance Tuning Practical Workshop, I came across a situation where a user was building a dynamic string for WHERE conditions as they were not aware of what conditions will be passed to the SELECT statement. This had created a complex situation for them as they had over 10 different conditions.

At any point of time they were not sure which WHERE condition will be applicable and there were chances that more than one condition also can be applied to the query. Finally, they had used dynamic query and which was providing them a very poor performance. We could have spent countless hours to optimize their performance for dynamic SQL, but the better option would have been us the CASE expression in the WHERE clause.

In this example, I will only two columns and will demonstrate to you how you can write a dynamic SQL like query based on if the condition has value or not.

First, let us create a sample table.

USE tempdb
GO
CREATE TABLE Contacts(ID INT, FirstName VARCHAR(100), LastName VARCHAR(100))
INSERT INTO Contacts (ID, FirstName, LastName)
SELECT 1, 'Pinal', 'Dave'
UNION ALL
SELECT 2, 'Mark', 'Smith'
UNION ALL
SELECT 3, 'Mohan', 'Shah'
UNION ALL
SELECT 4, 'Matt', 'Alexus'
UNION ALL
SELECT 5, 'Roger', 'Croc'
GO

SELECT FirstName, LastName
FROM Contacts
GO

Now we will see our solution with a CASE expression in the WHERE clause. Please note in this example, I have kept the firstname column empty. I have passed a value in the last column. This simulates the situation where only one condition has value and another one is empty.

DECLARE @FirstName VARCHAR(100)
SET @FirstName = ''

DECLARE @LastName VARCHAR(100)
SET @LastName = 'Dave'  

SELECT FirstName, LastName
FROM Contacts
WHERE   
	FirstName = CASE 
	WHEN LEN(@FirstName) > 0 THEN  @FirstName 
	ELSE FirstName 
	END 
AND 
	LastName = CASE 
	WHEN LEN(@LastName) > 0 THEN  @LastName 
	ELSE LastName 
	END 
GO

You can clearly see how with the help of CASE expressions, we can create a situation where without using dynamic SQL we can selectively apply where condition.

Reference: Pinal Dave (https://blog.sqlauthority.com)