Where case statement

Mark Bakelaar
Mark Bakelaar used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Analyst
Commented:
The issue is that your ELSE is not even evaluated because you already have a "Administrator" or "Superuser" and you need this "User to be in a CASE but a UNION as well - something like:

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] ='User') THEN
    'User'
  ELSE
      'No User found'
 END
UNION
 SELECT [UserID], [UserName], [FullName], [Password], [UserAccessID], [CompanyID], [UserRole]
FROM [Users]
WHERE UserRole =
  CASE WHEN EXISTS (SELECT * FROM [Users] WHERE [UserName] = @username and [UserRole] ='Superuser') THEN
    'Superuser'
  WHEN EXISTS (SELECT * FROM [Users] WHERE [UserName] = @username and [UserRole] ='User') THEN
    'User'
  ELSE
      'No User found'
 END

ORDER BY [UserName] ASC

Author

Commented:
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
lcohanDatabase Analyst

Commented:
--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

Author

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial