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
Best Answer
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:Expanding to your three conditions, you need to use multiple expressions because you can't use
CASE
, again, to change>=
to<
. So something like:Or you can use the OR conditionals as mentioned in the comments:
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:
(And in case you're wondering why I use
COALESCE
instead ofISNULL
. That said, theCOALESCE
/ISNULL
is really meaningless here, because theCOALESCE
d value (0
) will only potentially meet the< 25000
criteria - and I'm not sure that's what you intended at all.)