CODEWITHSUNDEEP
sqlsql-servert-sql
bmsqldev
bmsqldev

Reputation: 2735

Use Multiple conditions in Case Statement

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?

Upvotes: 3

Views: 358

Answers (4)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

This is no answer. I just want to show how CASE WHEN works, so you see your mistake. Your query evaluates case @budgetidas follows:

  1. when 7 then 7 => take a 7 for a @budgetid 7
  2. when 7 then 8 => ignored, because we already said to take 7 for a @budgetid 7
  3. when 7 then 10 => ignored, because we already said to take 7 for a @budgetid 7
  4. when 8 then 6 => take a 6 for a @budgetid 8
  5. end => no else here, so any other @budgetid results in NULL.

You then compare the result with = budgetid. This is never true for NULL. So you end up with:

where (@budgetid = 7 and budgetid = 7)
   or (@budgetid = 8 and budgetid = 6)

Upvotes: 2

Ragul
Ragul

Reputation: 512

This will work !!

SELECT
    officied
FROM
    budgettable
WHERE
    1 = 1
AND
    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

Better solution : Add a new column is_budget_calculated (Data type BIT) and update it as 0 and remaining as 1.

Upvotes: 1

Andomar
Andomar

Reputation: 238086

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.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269803

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.

Upvotes: 4

Related Questions