CASE
is an expression that returns a single value. You are attempting to use it as control of flow logic to optionally include a filter, and it doesn't work that way. Maybe something like this would work:
AND COALESCE(Project.Contribution, 0) >= CASE @BudgetFilter
WHEN 0 THEN 25000 ELSE 2000000000 END
-- guessing that 2 billion is enough to always be greater
-- than the highest valid contribution
Expanding to your three conditions, you need to use multiple expressions because you can't use CASE
, again, to change >=
to <
. So something like:
AND COALESCE(Project.Contribution, 0) >= CASE @BudgetFilter
WHEN 0 THEN 25000 ELSE 2000000000 END
AND COALESCE(Project.Contribution, 0) < CASE
@BudgetFilter WHEN 1 THEN 25000 WHEN 2 THEN 0 ELSE -2000000000 END)
Or you can use the OR conditionals as mentioned in the comments:
AND ((@BudgetFilter = 0 AND Project.Contribution >= 25000)
OR (@BudgetFilter IN (1,2) AND Project.Contribution < CASE
@BudgetFilter WHEN 1 THEN 25000 ELSE 0 END))
Or you can just use dynamic SQL, which may be useful if you have a lot of other criteria and/or you are having difficulty getting consistent behavior from the plans for the above variations:
DECLARE @sql NVARCHAR(MAX) = N'SELECT ...
WHERE ...
AND COALESCE(Project.Contribution, 0) '
+ CASE @BudgetFilter
WHEN 0 THEN ' >= 25000'
WHEN 1 THEN ' < 25000'
WHEN 2 THEN ' < 0' END + ';';
EXEC sp_executesql @sql;
(And in case you're wondering why I use COALESCE
instead of ISNULL
. That said, the COALESCE
/ISNULL
is really meaningless here, because the COALESCE
d value (0
) will only potentially meet the < 25000
criteria - and I'm not sure that's what you intended at all.)
THEN
part of theCASE
statement is aSET
operation, so you cannot use an operator in it. Can you provide a little more info as to what you are expecting to see as a result?AND ((@BudgetFilter = 0 AND ISNULL(Project.Contribution, 0) >= 25000) OR (@BudgetFilter = 1 AND ISNULL(Project.Contribution, 0) < 25000) AND...