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 ','.
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)