Question: How to Use a CASE statement in the WHERE Clause?
Answer: First of all – CASE is not a statement but rather an expression so the right question should be How to Use a CASE expression in the WHERE Clause? In any case, let us see the answer to this question.
Lots of people think that case can be used in SELECT and ORDER BY clause but not in the WHERE clause. The truth is that it can be used anywhere in the entire SELECT statement. Let us see a very simple script which I have built based on the sample database.
SELECT InvoiceID, CustomerID, AccountsPersonID, ContactPersonID FROM [WideWorldImporters].[Sales].[Invoices] WHERE 1 = CASE WHEN ContactPersonID = AccountsPersonID THEN 1 ELSE 0 END GO
You can see in the example above how we have used the CASE expression in the WHERE condition. Remember, the example above is just for the illustration purpose of the CASE in the WHERE clause. I know that I honestly do not need the CASE there and I could simplify the entire statement as WHERE ContactPersonID = AccountsPersonID.
I hope that the above example is very clear.
I request not to leave comments with the following two observations.
#1 CASE is not a statement but it is an expression – we know it.
#2 WHERE can be just simplified with ContactPersonID = AccountsPersonID – we know it.
Here are a few additional interview questions and answers which you may find interesting.
- How to Create a Table Automatically in All Newly Created Databases? – Interview Question of the Week #238
- How to Skip Top N Rows in SQL Server? – Interview Question of the Week #237
- How to Write in Text File from T-SQL in SQL Server? – Interview Question of the Week #236
- How to Remove Quotes of QUOTENAME? – Interview Question of the Week #235
- Can an Index Reduce Performance of SELECT Query? – Interview Question of the Week #234
- How to Find Definition of Computed Column in SQL Server using T-SQL? – Interview Question of the Week #233
- What is Source_Database_ID in Sys.Databases?- Interview Question of the Week #232
Reference: Pinal Dave (https://blog.sqlauthority.com)
Thanks Pinal !for such a useful observation!
With the CASE expression, can a parameter / filter be optional in the WHERE clause?
DECLARE @PersonId INT;
SET @PersonId = 3;
WHERE Active = 1
AND CASE ISNULL(@PersonId, 0) 0 THEN PersonId = @PersonId ELSE 0 END;
The previous SELECT brings errors, but I’m wondering if such changes can be done.
when i have more than one condition, i.e. i have @parametr and i try something like this:
select … from table where
when @parametr=1 then select data from table1
when @parametr=2 then select data from table2
real example: (with @parametr)
select something from table where SomethingIsActive=1 and
when @parametr=1 then somethingNr in (1,2,3)
when @parametr=2 then somethingNr in (1,2,3,4,5,6)