SQL use CASE statement in WHERE IN clause
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).
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.
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)
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!