SQL Server stored procedures, returning multiple value in Else statement inside CASE clause

Question

I want to use a CASE and IN clause inside a where clause

• When User Status = 1, CASE should return 1
• When User Status = 2, CASE should return 2
• When User Status = 3, CASE should return 3
• When User.Status = NULL, it should show all the user with status 1, 2 and 3

But the below query is throwing a syntax error.

QUERY:

SELECT *
FROM User
where isInternalUser = 0 and DeleteMark <> 1 and
User.Status IN 
(
   CASE @UserStatus          // case for UserStatus
       WHEN 0 THEN 0
       WHEN 1 THEN 1
       WHEN 2 THEN 2
       ELSE (0,1,2)           
    END                      
)

Could anyone help me out..

Thanks, Aneesh

Answer 1

User.Status IN 
(
    SELECT s FROM (
        SELECT 1 s union SELECT 2 s union SELECT 3 s
    ) a
    WHERE @UserStatus is null or @UserStatus = s
)

Answer 2

Try without the CASE statement, but checking whether @UserStatus IS NULL or not:

SELECT *
FROM User
WHERE isInternalUser = 0 AND DeleteMark <> 1 AND (
    (@UserStatus IS NOT NULL AND Status = @UserStatus)
    OR (@UserStatus IS NULL AND Status IN (0, 1, 2))
)