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.
CodePudding user response:
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 thanselect -1
.
CodePudding user response:
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')