1

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

  • The THEN part of the CASE statement is a SET 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? – RoKa May 17 '13 at 15:46
  • 2
    It should probably look more like this: AND ((@BudgetFilter = 0 AND ISNULL(Project.Contribution, 0) >= 25000) OR (@BudgetFilter = 1 AND ISNULL(Project.Contribution, 0) < 25000) AND... – mustaccio May 17 '13 at 15:54
6

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.)

  • Thank you all for all the answers. You've set me on the right path :). I settled on using the following approach: AND ((@BudgetFilter = 0 AND ISNULL(Project.Contribution, 0) >= 25000) OR (@BudgetFilter = 1 AND ISNULL(Project.Contribution, 0) < 25000) OR (@BudgetFilter = 2 AND ISNULL(Project.Contribution, 0) > 0)) – Alex May 17 '13 at 17:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.