using CASE with ISNULL 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) &gt;= 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, &lt;25K, &lt;0 respectively.
There are other filters(dropdown value selections) that i am working once I get past this issue.

Thanks.
A
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) &gt;= 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 &gt;= to &lt;. So something like:

AND COALESCE(Project.Contribution, 0) &gt;= CASE @BudgetFilter
  WHEN 0 THEN 25000 ELSE 2000000000 END
AND COALESCE(Project.Contribution, 0) &lt; 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 &gt;= 25000)
  OR (@BudgetFilter IN (1,2) AND Project.Contribution &lt; 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 ' &gt;= 25000'
  WHEN 1 THEN ' &lt; 25000'
  WHEN 2 THEN ' &lt; 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 &lt; 25000 criteria - and I'm not sure that's what you intended at all.)