asked on

Where case statement

Dear expert,

I need the logged in user to be able to see other users based on his user role. If he is administrator he should see all, if he is superuser he should see other super users and user, if he is user he should only see other users. The below query returns only rows with the same user role, but not with a "lower" user role.

Can someone point be in the right direction?

Thanks for any help.
MB

Use VRS

DECLARE @UserName as varchar(59)
SET @UserName ='mb'

SELECT [UserID], [UserName], [FullName], [Password], [UserAccessID], [CompanyID], [UserRole]
FROM [Users]
WHERE UserRole =
CASE WHEN EXISTS (SELECT * FROM [Users] WHERE [UserName] = @username and [UserRole] ='Administrator') THEN
'Administrator'
WHEN EXISTS (SELECT * FROM [Users] WHERE [UserName] = @username and [UserRole] ='Superuser') THEN
'Superuser'
ELSE
'User'
END
ORDER BY [UserName] ASC

ASKER CERTIFIED SOLUTION

lcohan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark Bakelaar

ASKER

Thanks for the quick reply.

Is there also a possibility in SQL like below?

WHERE UserRole =
CASE WHEN EXISTS (SELECT * FROM [Users] WHERE [UserName] = @username and [UserRole] ='Administrator') THEN
'Administrator' OR 'Superuser' OR 'User'

MB

lcohan

--Try rather:
DECLARE @UserName as varchar(59)
SET @UserName ='mb'

SELECT [UserID], [UserName], [FullName], [Password], [UserAccessID], [CompanyID], [UserRole]
FROM [Users]
WHERE UserRole =
CASE WHEN EXISTS (SELECT * FROM [Users] WHERE [UserName] = @username and [UserRole] in ('Administrator','Superuser','User'))
THEN [UserRole]
ELSE
'No User found'
END

Mark Bakelaar

ASKER

Thanks for your help, I ended up with the following.

Use VRS
DECLARE @UserName as varchar(59)
SET @UserName ='mb'

SELECT [UserID], [UserName], [FullName], [Password], [UserAccessID], [CompanyID], [UserRole]
FROM [Users]
WHERE UserRole =
CASE WHEN EXISTS (SELECT [UserID] FROM [Users] WHERE [UserName] = @username and [UserRole] IN ('Administrator')) THEN
'administrator'
END
UNION
SELECT [UserID], [UserName], [FullName], [Password], [UserAccessID], [CompanyID], [UserRole]
FROM [Users]
WHERE UserRole =
CASE WHEN EXISTS (SELECT [UserID] FROM [Users] WHERE [UserName] = @username and [UserRole] IN ('Administrator','Superuser'))
THEN
'Superuser'
END
UNION
SELECT [UserID], [UserName], [FullName], [Password], [UserAccessID], [CompanyID], [UserRole]
FROM [Users]
WHERE UserRole =
CASE WHEN EXISTS (SELECT [UserID] FROM [Users] WHERE [UserName] = @username and [UserRole] IN ('Administrator','Superuser','User'))
THEN
'User'
END
ORDER BY [UserName] ASC