SQL use CASE statement in WHERE IN clause

Question

Is it posible to use case in where in clause? Something like this:

 DECLARE @Status VARCHAR(50);
 SET @Status='published';

 SELECT * FROM Product P    
 WHERE P.Status IN (CASE WHEN @Status='published' THEN (1,3)
                                   WHEN @Status='standby' THEN (2,5,9,6)
                                   WHEN @Status='deleted' THEN (4,5,8,10)
                                   ELSE (1,3)
                                   END)

This code gives the error : Incorrect syntax near ','.

You can use `case` in a `where`, but not like that. `Case` has to return one value per statement. — Sean, Oct 09 '13 at 12:23
@SeanCoetzee. I don't understand. Can you give me an example? — POIR, Oct 09 '13 at 12:27

juergen d · Accepted Answer · 2016-01-14T08:20:38.630

42

No you can't use case and in like this. But you can do

SELECT * FROM Product P    
WHERE @Status='published' and P.Status IN (1,3)
or @Status='standby' and P.Status IN  (2,5,9,6)
or @Status='deleted' and P.Status IN (4,5,8,10)
or P.Status IN (1,3)

BTW you can reduce that to

SELECT * FROM Product P    
WHERE @Status='standby' and P.Status IN (2,5,9,6)
or @Status='deleted' and P.Status IN (4,5,8,10)
or P.Status IN (1,3)

since or P.Status IN (1,3) gives you also all records of @Status='published' and P.Status IN (1,3)

edited Jan 14 '16 at 08:20

answered Oct 09 '13 at 12:26

juergen d

201,996
37
293
362

2

Don't you need parenthesis around each "or" clause? E.g. or (@Status='standby' and P.Status IN (2,5,9,6)) – dcp Oct 09 '13 at 12:32
4

Don't think you need parentheses, but the last `or` statement will return false results... See this fiddle -- the 2nd record should not be returned. http://sqlfiddle.com/#!3/9aef0/6 – sgeddes Oct 09 '13 at 12:34
AND has a stronger precedence than OR. That is why you don't need parentheses in this case. – juergen d Jul 07 '21 at 11:16

score 13 · Answer 2 · answered Oct 09 '13 at 12:43

I realize this has been answered, but there is a slight issue with the accepted solution. It will return false positives. Easy to fix:

SELECT * FROM Products P    
WHERE (@Status='published' and P.Status IN (1,3))
   or (@Status='standby' and P.Status IN  (2,5,9,6))
   or (@Status='deleted' and P.Status IN (4,5,8,10))
   or (@Status not in ('published','standby','deleted') and P.Status IN (1,2))

SQL Fiddle Demo

Parentheses aren't needed (although perhaps easier to read hence why I included them).

score 3 · Answer 3 · answered Dec 07 '17 at 20:04

3

I believe you can use a case statement in a where clause, here is how I do it:

Select 
ProductID
OrderNo,
OrderType,
OrderLineNo
From Order_Detail
Where ProductID in (
Select Case when (@Varibale1 != '') 
then (Select ProductID from Product P Where .......)
Else (Select ProductID from Product)
End as ProductID
)

This method has worked for me time and again. try it!

answered Dec 07 '17 at 20:04

Harry Grewal

59
8

It worked for me, created a temp table inserted the required products based on parameter condition and joined with temp table, deleted the temp table at last – Ramesh Venkataswamy Oct 05 '20 at 17:05

score 1 · Answer 4 · edited Jul 09 '18 at 12:24

1

 SELECT  * FROM Tran_LibraryBooksTrans LBT  
 LEFT JOIN Tran_LibraryIssuedBooks LIB ON 
 CASE WHEN LBT.IssuedTo='SN' AND LBT.LIBRARYTRANSID=LIB.LIBRARYTRANSID THEN 1
      WHEN LBT.IssuedTo='SM' AND LBT.LIBRARYTRANSID=LIB.LIBRARYTRANSID THEN 1 
      WHEN LBT.IssuedTo='BO' AND LBT.LIBRARYTRANSID=LIB.LIBRARYTRANSID THEN 1
 ELSE 0 END

edited Jul 09 '18 at 12:24

Sowndarya

97
1
15

answered Jun 12 '15 at 11:09

RAJ

19
1

score 1 · Answer 5 · answered Jun 28 '16 at 04:31

maybe you can try this way

SELECT * FROM Product P WHERE (CASE WHEN @Status = 'published' THEN (CASE WHEN P.Status IN (1, 3) THEN 'TRUE' ELSE FALSE END) WHEN @Status = 'standby' THEN (CASE WHEN P.Status IN (2, 5, 9, 6) THEN 'TRUE' ELSE 'FALSE' END) WHEN @Status = 'deleted' THEN (CASE WHEN P.Status IN (4, 5, 8, 10) THEN 'TRUE' ELSE 'FALSE' END) ELSE (CASE WHEN P.Status IN (1, 3) THEN 'TRUE' ELSE 'FALSE' END) END) = 'TRUE'

In this way if @Status = 'published', the query will check if P.Status is among 1 or 3, it will return TRUE else 'FALSE'. This will be matched with TRUE at the end

Hope it helps.

score 1 · Answer 6 · answered Oct 24 '20 at 05:29

According to my Scenario, I did "use CASE statement in WHERE IN clause" like following

@AdjType varchar(20) = 'Value', 
@Base varchar(20) = 'Common'
where 
(select CASE WHEN SA.IsPersentage = 0 THEN 'Value' 
       WHEN SA.IsPersentage = 1 THEN 'Presentage' END) Like @AdjType
and (Select CASE WHEN SA.IsDependOnBasicSalary = 0 THEN 'Common' 
        WHEN SA.IsDependOnBasicSalary = 1 THEN 'Basic Salary' END) like @Base

score 0 · Answer 7 · edited Jun 12 '15 at 16:09

 select  * from Tran_LibraryBooksTrans LBT  left join
 Tran_LibraryIssuedBooks LIB ON   case WHEN LBT.IssuedTo='SN' AND
 LBT.LIBRARYTRANSID=LIB.LIBRARYTRANSID THEN 1 when LBT.IssuedTo='SM'
 AND LBT.LIBRARYTRANSID=LIB.LIBRARYTRANSID THEN 1 WHEN
 LBT.IssuedTo='BO' AND LBT.LIBRARYTRANSID=LIB.LIBRARYTRANSID THEN 1
 ELSE 0 END`enter code here`select  * from Tran_LibraryBooksTrans LBT 
 left join Tran_LibraryIssuedBooks LIB ON   case WHEN LBT.IssuedTo='SN'
 AND LBT.LIBRARYTRANSID=LIB.LIBRARYTRANSID THEN 1 when
 LBT.IssuedTo='SM' AND LBT.LIBRARYTRANSID=LIB.LIBRARYTRANSID THEN 1
 WHEN LBT.IssuedTo='BO' AND LBT.LIBRARYTRANSID=LIB.LIBRARYTRANSID THEN
 1 ELSE 0 END

SQL use CASE statement in WHERE IN clause

7 Answers7

Linked

Related