Interview question: Can you use CASE in a SQL Server WHERE clause when search conditions are optional?
Answer: Yes. CASE returns a value that a WHERE predicate can compare. Kalen Delaney rightly corrected my wording: CASE is an expression, not a control-of-flow statement. For simple optional equality filters, an explicit @p IS NULL OR column = @p predicate is usually clearer. Neither spelling automatically guarantees a fast plan.
This came up in my performance tuning workshop. A team had more than ten possible search conditions and was assembling a long dynamic string because it did not know which inputs would arrive. I used two names to show the basic idea. The actual choice of query form still needs a plan and workload test.
Here is a self-contained version of that small example. A NULL parameter means “do not filter on this field”:
-- Open a fresh query window; do not replace an existing temporary table.
IF OBJECT_ID(N'tempdb..#Contacts') IS NOT NULL
THROW 50126, 'Temporary demo table exists. Open a fresh window.', 1;
CREATE TABLE #Contacts
(
ID int NOT NULL,
FirstName varchar(100) NULL,
LastName varchar(100) NULL
);
INSERT INTO #Contacts (ID, FirstName, LastName)
VALUES (1, 'Pinal', 'Dave'),
(2, 'Mark', 'Smith'),
(3, 'Mohan', 'Shah'),
(4, 'Matt', 'Alexus'),
(5, 'Roger', 'Croc');
DECLARE @FirstName varchar(100) = NULL;
DECLARE @LastName varchar(100) = 'Dave';
SELECT FirstName, LastName
FROM #Contacts
WHERE (@FirstName IS NULL OR FirstName = @FirstName)
AND (@LastName IS NULL OR LastName = @LastName);
-- Historical CASE version, using the same five supplied non-NULL names.
DECLARE @LegacyFirstName varchar(100) = '';
DECLARE @LegacyLastName varchar(100) = 'Dave';
SELECT FirstName, LastName
FROM #Contacts
WHERE FirstName = CASE WHEN LEN(@LegacyFirstName) > 0
THEN @LegacyFirstName ELSE FirstName END
AND LastName = CASE WHEN LEN(@LegacyLastName) > 0
THEN @LegacyLastName ELSE LastName END;
DROP TABLE #Contacts;The result is Pinal Dave. The original screenshot below shows the same result from my older CASE-expression version. I kept it because it is readable and shows the code with its actual result:
The older predicate used FirstName = CASE WHEN LEN(@FirstName) > 0 THEN @FirstName ELSE FirstName END. It demonstrates that CASE can appear inside WHERE, but it has a trap: when FirstName is NULL and the filter is absent, FirstName = FirstName is unknown, so that row disappears. The explicit OR form above expresses the intended optional behavior more accurately. If an empty string means “not supplied” in your application, normalize it to NULL before the query.
For a large table, compare actual plans and reads for common parameter combinations. Dynamic SQL can be appropriate when it is parameterized and tested; CASE is not inherently faster. SQL Server 2025 can use optional parameter plan optimization for eligible predicates at compatibility level 170, but plan choice still depends on the query and database settings.
Optional parameter plan optimization documents the newer plan behavior.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
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!!!