Use Multiple conditions in Case Statement

Question

I have implemented multiple conditions in case statement as below,

select officied from budgettable
where budgetid = case @budgetid when 7 then 7
                when 7 then  8
                when 7 then 10
                when 8 then 6
          end

but it didn't give me any result. If I pass budget-Id as 7 the query should return 8,10,7 budget id's. Anything wrong in the above query?

Answer 1

I suspect that you want something like this:

where budgetid = @budgetid or
      (@budgetid = 7 and budgetid in (7, 8, 10)) or
      (@budgetid = 8 and budgetid = 6)

Your query is failing because for all values of @budgetid other than 7 or 8, the case returns NULL -- which is treated as false.

Answer 2

One option is to use the case as a boolean expression, returning 1 when your conditions are met:

select officied from budgettable
where 1 = case 
      when @budgetid = budgetid then 1
      when @budgetid = 7 and budgetid in (7,8,10) then 1 
      when @budgetid = 8 and budgetid in (6,8) then 1 
      end

This expands the results returned by @budgetid 7 to include 8 and 10.