Why can't I use a boolean expression in a SQL CASE expression inside a WHERE clause?

Question

I want to write a query with the following logic:

• If @fundKey is greater than 0, look for funds with a key matching @fundKey.
• Otherwise, look for funds with a key less than 1000000.

If I were to write this logic in JavaScript it would be something like this:

funds.filter(x => fundKey > 0 ? x.FundKey === fundKey : x.FundKey < 1000000)

I tried to write a query like this

DECLARE @fundKey INT = -1;
SELECT FundKey, FundName FROM dbo.Funds
WHERE CASE WHEN @fundKey > 0 THEN FundKey = @fundKey ELSE FundKey < 1000000 END

but it seems that the syntax is invalid. Why can't I use the result of a boolean expression in a WHERE clause? Is there a different way to write this logic without being too verbose?

Answer 1

Because a CASE expression returns a scalar value not a boolean result. Though, you shouldn't be use a CASE expression on a column in the WHERE anyway; it won't be SARGable. Use explicit AND and OR logic.

For your query, this means you should actually be doing:

DECLARE @fundKey INT = -1;

SELECT FundKey,
       FundName
FROM dbo.Funds
WHERE (@fundKey > 0 AND FundKey = @fundKey)
   OR (@fundKey <= 0 AND FundKey < 1000000);

Likely, as well, you'll want to add an OPTION (RECOMPILE) as the plans could be quite different for the 2 boolean clauses.