Using inline SELECT with IN operator

Question

I want to select from one table based on who run the SP. For example if executer has Management role the they can see all records but if executer is Employee then they just see records for all employees. There are some roles like Seller, StockKeeper , ... .

Please consider this sudo code, I want to write a code like this but I got error:

Declare @Role      varchar(30)

select * 
from MyTable 
where Status in (IIF(@Role = 'Employee', select -1 , select -1, 0, 1))

OR

select * 
from MyTable 
where Status in (case @Role when 'Employee' then select -1 else select -1, 0 , 1 end)

Error:

Incorrect syntax near the keyword 'select'.

Incorrect syntax near the keyword 'else'.

Incorrect syntax near ')'.

Is there any way to combine inline SELECT and IN operator?

Thanks

---

Edit 1)

Sample Data:

Id       Value        Status
----------------------------
1         10            -1
2         20            0
3         30            -1
4         40            1
5         50            -1
6         60            0
7         70            1
8         80            -1

for Employee I want to get this result:

Id       Value        Status
----------------------------
1         10            -1
3         30            -1
5         50            -1
8         80            -1

for Manager I want to get All records.

Answer 1

Use UNION to build the full list of allowable statuses in a conditional manner:

SELECT * 
FROM MyTable 
WHERE [Status] IN (
  -- Everyone gets this role
  SELECT -1
  UNION ALL
  -- Only special people get this role
  SELECT 0
  WHERE @Role <> 'Employee'
  -- Only special people get this role
  UNION ALL
  SELECT 1
  WHERE @Role <> 'Employee'
);

Assuming your sample data is reflective of the bigger picture you could simplify that down to:

SELECT * 
FROM MyTable 
-- Everyone gets this status
WHERE [Status] = -1
-- Only special people get these statuses
OR (@Role <> 'Employee' AND [Status] IN (0, 1);

Notes:

• CASE is an expression i.e. returns a scalar value. Its not a switch statement.
• Sub-queries require brackets around them, so even if case allowed it you would still need (select -1) rather than select -1.

Answer 2

If I understand correctly, you can try to use conditions to judge your expected result.

if your input @Role is Manager get all data, otherwise Employee will get -1

Declare @Role varchar(30)

SELECT *
FROM MyTable 
WHERE (@Role = 'Employee' AND Status = -1)
OR (@Role = 'Manager')

sqlfiddle