Avatar of Mark Bakelaar
Mark Bakelaar
Flag for Norway 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
Microsoft SQL Server

Avatar of undefined
Last Comment
Mark Bakelaar

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
lcohan

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes