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)
15 Comments. Leave new
Is it possible to choose column name in where conditions?
E.g. If fname len > 0 then
Firstname = fname
Else
Lastname = fname
IF conditions are not allowed in WHERE clause.
You can use two different SELECT statements
if fname len > 0 then
select columns from table where firstname= fname
Else
select columns from table where Lastname = fname
Love this! Will greatly help me reduce my dynamic SQL that I always have hated doing.
madhivanan – for your alternate method, you would need to include parenthesis around the first name and last name comparisons:
((FirstName = @FirstName) or (@FirstName=”))
AND
((LastName = @LastName) or (@LastName=”))
Otherwise, if you put ‘Pinal’ for @FirstName and ” for @LastName, all names would be returned.
I’ve used this method plenty but FYI, your mileage may vary. It can lead to some pretty dismal query plans– can force optimizer to do table scans depending on underlying data and parameters. Just be sure to test– maybe a UNION or another method may be better depending on case (pun totally intended).
Thank you
I am glad to hear that.
Can I go something like this?
DECLARE @pStatementGroup VARCHAR(50) = ‘ALL’
SELECT Master.*
FROM Master Master
WHERE Master.StatementGroup IN
(SELECT CASE
WHEN @pStatementGroup = ‘ALL’
THEN (‘Company1’, ‘Company2’, ‘Company3’, ‘Company4’, ‘Company5’)
ELSE WHEN @pStatementGroup = ‘MAINGROUP’
THEN (‘Company1’, ‘Company2’, ‘Company3’, ‘Company4’)
ELSE
THEN (‘Company5’)
END)
ORDER BY
Master.PolicyID
— below Stored Procedure declare manual enter start and end time
@startDate Datetime=null,
@EndDate Datetime=null,
—-Below Stored Procedure “Auto Declare” when @startDate and @Endate is null then select below Declare
DECLARE
@FirstDayOfLastMonth DATETIME = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0) , @FirstDayOfCurrMonth DATETIME = DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1)
—–Where Condition use but when i give the @start and @End Date that time @start and @End date given date data shows as well Auto declare Date also shows , but i want when i will give to @start and @end Date give manual then AutoDeclare Date are by Pass (not consider)
where (bc.SubmitDate between @FirstDayOfLastMonth and @FirstDayOfCurrMonth) or (bc.SubmitDate Between @StartDate and @EndDate)
i need a logic like the below in where condition in SQL Server
the second when has a different condition (i know the syntax is wrong, however the below query is to just show the logic i want to write).
WHERE (CASE WHEN @Diff=0 THEN Diff=Diff
WHEN @Diff=1 THEN Diff!=0
WHEN @Diff=2 THEN Diff=0 END)
Instead of doing a equality comparison as you do in your example, is it possible to do null comparisons? As in could one use “is null” or “is not null” as part of using case inside where?
Is there a way to run a completely different where clause based on a condition? I’ve read the CASE statement can’t be apart of the expression but I haven’t been able to figure out how to rewrite this if the condition is not based on a single field.
SELECT (list of columns)
FROM table x
WHERE
CASE WHEN (date1 > date2) THEN
— run this clause
(x.field1 = ( SELECT MAX(field2)
FROM b.table S2
WHERE S2.GUID = x.GUID
AND S2.id =x.id)
ELSE
— run this clause instead
(x.field3 = (SELECT MAX(field3)
FROM b.table S2
WHERE S2.GUID = x.GUID
AND S2.id = x.id)
END
Thank you in advance for your assistance.
Dear Pinal Dave,
I owe you for your article helping me a ton. I was making a search system on my database and was so frustrated looking for a clean solution. While searching on line, I stumbled upon this article and it gave me the solution to a monumental problem.
I have seen your articles earlier also. Used them, but did not comment selfishly.
But this time I cant resist complementing you …
Thanks a ton.
Thanks so much!!!