SQL use CASE statement in WHERE IN clause

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)