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)