using CASE with ISNULL

Question

I have a select statement which is followed by a union. Inside the select, there is a where clause. I am trying to add in the following code to the where clause but I'm having an issue getting it to work out:

declare @BudgetFilter int;
set @Budgetfilter = 0;

AND CASE @BudgetFilter WHEN 0 THEN ISNULL(Project.Contribution, 0) >= 25000 END

The error is with the >. Error message: "Incorrect syntax near '>'."

Some additional background. Budget filter may be one of 3 values. 0,1,2 which translate into >=25K, <25K, <0 respectively. There are other filters(dropdown value selections) that i am working once I get past this issue.

Thanks. A

Answer 1

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 COALESCEd value (0) will only potentially meet the < 25000 criteria - and I'm not sure that's what you intended at all.)