# "ListUsers" Stored Procedure

Description:

This stored procedure users' names, id's, and roles, based on the Role ID of the requesting user. If the requesting user is in Role ID 3 (Consultant), they only get to see their own user information; if the requesting user is in Role 2 (Project Manager), they get to see their own information as well as information on members of projects they manage. Finally, if the requesting user is in Role 1 (Administrator), they get to see all user information.

Definition:

    	CREATE PROCEDURE TT_ListUsers
    	(
    		@UserID int,
    		@RoleID int
    	)
    	AS

    	IF @RoleID = 1
    		BEGIN
    		SELECT
    			UserID,
    			UserName,
    			TT_Users.RoleID,
    			TT_Roles.Name 'RoleName'
    		FROM
    			TT_Users INNER JOIN TT_Roles ON TT_Users.RoleID = TT_Roles.RoleID
    		END
    	ELSE IF @RoleID = 2
    		BEGIN
    		SELECT DISTINCT
    			TT_Users.UserID,
    			TT_Users.UserName,
    			TT_Users.RoleID,
    			TT_Roles.Name 'RoleName'
    		FROM TT_Users
    		INNER JOIN TT_ProjectMembers ON TT_Users.UserID=TT_ProjectMembers.UserID
    		INNER JOIN TT_Projects ON TT_ProjectMembers.ProjectID=TT_Projects.ProjectID
    		INNER JOIN TT_Roles ON TT_Users.RoleID = TT_Roles.RoleID
    		WHERE	@UserID = TT_Projects.ManagerUserID OR @UserID = TT_Users.UserID

    		END
    	ELSE
    		SELECT
    			UserID,
    			UserName,
    			TT_Users.RoleID,
    			TT_Roles.Name 'RoleName'
    		FROM
    			TT_Users INNER JOIN TT_Roles ON TT_Users.RoleID = TT_Roles.RoleID
    		WHERE UserID = @UserID

Database Tables Used:

The primary key in the Projects table is the ProjectID identity field.

The primary key in the ProjectMembers table is a composite key including both the the ProjectID and UserID fields. This table represents a user's membership in a project; i.e., users should only enter time for projects they belong to.

There is a Foreign key relationship between this table and the Projects table on the ProjectID field, as well as a Foreign key relationship between this table and the Users table on the UserID field.

The primary key in the Users table is the UserID identity field.

The primary key in the Roles table is the RoleID identity field.