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.

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

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

SQL Function, SQL Scripts, SQL Server
Previous Post
How to Add Column at Specific Location in Table? – Interview Question of the Week #126
Next Post
What is Copy Only Backup in SQL Server? – Interview Question of the Week #128

Related Posts

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

    Reply
    • IF conditions are not allowed in WHERE clause.

      Reply
    • 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

      Reply
  • Love this! Will greatly help me reduce my dynamic SQL that I always have hated doing.

    Reply
  • 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.

    Reply
  • 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).

    Reply
  • Thank you

    Reply
  • 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

    Reply
  • — 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)

    Reply
  • 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)

    Reply
  • 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?

    Reply
  • 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.

    Reply
  • Devesh Kumar Singh
    June 25, 2021 2:05 am

    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.

    Reply

Leave a Reply