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

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?

In T-SQL, CASE is an expression that returns a value. It is not for control of flow. Therefore you must say:

WHERE FundKey = CASE WHEN @fundKey >  0 THEN @fundKey END
   OR FundKey < CASE WHEN @fundKey <= 0 THEN 1000000  END

Since the implicit ELSE NULL can't match to = or <, only one condition can be true for any given row.

Less intuitive, and slightly wordier, but potentially better performance-wise:

WHERE FundKey BETWEEN 
         CASE WHEN @fundKey > 0 THEN @fundKey ELSE 0      END -- or 1?
     AND CASE WHEN @fundKey > 0 THEN @fundKey ELSE 999999 END

If you find performance still suffers (which is potentially the case at scale in general or specifically if FundKey isn't unique and you have significant skew), you can build the command dynamically, so that you're only performing a scan of all those matching rows when @fundKey is 0.

DECLARE @sql nvarchar(max) = N'SELECT FundKey, FundName FROM dbo.Funds'
  + CASE WHEN @fundKey > 0 THEN N' WHERE FundKey = @fundKey'
                           ELSE N' WHERE FundKey < 1000000';

EXEC sys.sp_executesql;

You can achieve similar with OPTION (RECOMPILE) and no dynamic SQL, but if the direct point lookup is common, or there are potentially other predicates that will be added in, or the query will be run a lot, dynamic SQL will provide long-term stability of small number of plans without constant recompiles.

For a lot more on CASE, see:

• Dirty Secrets of the CASE Expression

For more on dynamic SQL for handling shifting predicates (I call this "the kitchen sink"):

• An Updated Kitchen Sink Example

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.

This is another way to apply your logic in WHERE clause

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

Since CASE expressions are not sargable in WHERE clause, so the performance of this query could be not good as using explicit AND and OR logic. It's just a demonstration of how we could use CASE expression.