Using CASE expression inside a where clause

Question

I have a stored proc with 2 parameters, both integer

I need to use case statement inside where clause but i could not get it right

where 
dbo.StockTransfer.BranchId = @branchId 
AND
CASE WHEN IsNumeric(@roleId) = 1 
THEN 
dbo.StockTransfer.StatusId !=12    
ELSE
dbo.StockTransfer.StatusId NOT in (12, 13)  
  END

order by dbo.StockTransfer.StatusId ASC

I am getting an error in 'dbo.StockTransfer.StatusId !=12'

Msg 102, Level 15, State 1, Procedure GetDeliveryList, Line 44 [Batch Start Line 0]
Incorrect syntax near '!'.

This is a common misunderstanding of the case expression. It is used to return a scalar value. You are trying to use to control logical flow which it cannot do. Also be careful with IsNumeric. It returns true for some very interesting values that are clearly not numbers. ;) — Sean Lange, Oct 24 '19 at 15:25
Hi @sean Lange thanks for the information! i have 3 statusId (12,13 and 14) and what i am trying to achieve is if the roleId = 1 i should only return those records with statusId 13 and 14 and if the roleId is not equal to 1 i should only return those record with statusId 14. is there any possible way to do this? — axioxijhel, Oct 24 '19 at 15:28
[This](https://stackoverflow.com/a/10260297/92546) answer is applicable to both `join` conditions and `where` clauses. And it's a `case` _expression_, not _statement_. As Sean Lange mentioned, [`IsNumeric()`](https://learn.microsoft.com/en-us/sql/t-sql/functions/isnumeric-transact-sql) is notoriously [problematic](http://www.sqlservercentral.com/articles/ISNUMERIC()/71512/). — HABO, Oct 24 '19 at 15:37
@HABO - LOL the OP seems to suffer calling it both and expression and a statement just like MS. They have expression in the question title, but statement in the question body. Although the newest MS documentation has finally fixed the ambiguity there. — Sean Lange, Oct 24 '19 at 15:42

score 2 · Answer 1 · answered Oct 24 '19 at 15:00

Don't bother. Just use regular logic:

where dbo.StockTransfer.BranchId = @branchId and
      ( (IsNumeric(@roleId) = 1 and 
         dbo.StockTransfer.StatusId <> 12
        ) or
        (IsNumeric(@roleId) = 0 and
         dbo.StockTransfer.StatusId not in (12, 13)
        )
     )

This doesn't handle NULL values, but that can easily be added.

Actually, I might simplify this to:

where dbo.StockTransfer.BranchId = @branchId and
      dbo.StockTransfer.StatusId <> 12 and
      (IsNumeric(@roleId) = 1 or
       dbo.StockTransfer.StatusId <> 13
      )

thanks for the help! actually i have 3 statusId (12,13 and 14) and what i am trying to achieve is if the roleId = 1 i should only return those records with statusId 13 and 14 and if the roleId is not equal to 1 i should only return those record with statusId 14 — axioxijhel, Oct 24 '19 at 15:23

score 0 · Accepted Answer · answered Oct 24 '19 at 15:44

Given the newest comments about what you want to return I think it would be something like this. I added lots of white space for clarity on the parenthesis.

where dbo.StockTransfer.BranchId = @branchId 
    AND 
    (
        ( 
            @roleId = 1
            AND
            StatusId in (13, 14)
        )
        OR
        (
            @roleId <> 1
            AND
            StatusId = 14
        )
    )

Using CASE expression inside a where clause

2 Answers2