user_role:
1) user_role_id
2) user_code
3) role_name
4) parent_role_id
Users
1)UserId
2)FirstName
3)LastName
4)UserRole
When we select admin role, so query should returns admin users,
When we select supervisor role, so query should returns admin users and supervisor users,
when we select staff role, so query should return supervisor users.
Following is my stored procedure, it's working fine but i want another solution.I am new in sql server.
CREATE PROCEDURE [get_parent_users]
@UserRole INT
AS
BEGIN
DECLARE @roleCode AS VARCHAR (50);
SELECT @roleCode = role_code
FROM user_role
WHERE user_role_id = @UserRole;
IF @roleCode = 'sysadmin'
BEGIN
SELECT u.UserId,
u.FirstName,
u.LastName,
u.UserRole,
ur.role_name
FROM Users AS u
INNER JOIN
user_role AS ur
ON ur.user_role_id = u.UserRole
WHERE ur.parent_role_id = 1
AND ur.user_role_id = @UserRole;
END
IF @roleCode = 'supervisor'
BEGIN
SELECT u.UserId,
u.FirstName,
u.LastName,
u.UserRole,
ur.role_name
FROM Users AS u
INNER JOIN
user_role AS ur
ON ur.user_role_id = u.UserRole
WHERE u.UserRole = (SELECT parent_role_id
FROM user_role
WHERE user_role_id = @UserRole);
END
IF @roleCode = 'staff'
BEGIN
SELECT u.UserId,
u.FirstName,
u.LastName,
u.UserRole,
ur.role_name
FROM Users AS u
INNER JOIN
user_role AS ur
ON ur.user_role_id = u.UserRole
WHERE u.UserRole = (SELECT parent_role_id
FROM user_role
WHERE user_role_id = @UserRole);
END
END
Thanks in advance
Piyush PansuriyaPosted Oct 30, 2018, 1:13 AM