# "ListProjectsWithMembership" Stored Procedure

Description:

This stored procedure returns a list of project information based on the Role ID of the user making the request, as well as the User Id being requested. If the requesting user is in Role 3 (Consultant), they can only see their own projects; if the requesting user is in Role 2 (Project Manager), they can see all of the projects for the user that they manage.

Finally, if the requesting user is in Role 1 (Administrator), they can see all projects for the user.

Definition:

    	CREATE   PROCEDURE TT_ListProjectsWithMembership
    	(
    		@QueryUserID int,
    		@UserID int
    	)
    	AS

    	DECLARE @@QueryUserRoleID int
    	SELECT @@QueryUserRoleID = TT_Users.RoleID FROM TT_Users WHERE TT_Users.UserID = @QueryUserID

    	IF @@QueryUserRoleID = 2
    	BEGIN
    		SELECT 	TT_Projects.ProjectID,
    			Name,
    			Description,
    			ManagerUserID,
    			EstCompletionDate,
    			EstDuration
    		FROM TT_Projects
    		INNER JOIN TT_ProjectMembers ON TT_ProjectMembers.ProjectID = TT_Projects.ProjectID
    		WHERE UserID = @UserID AND ManagerUserID = @QueryUserID
    	END
    	IF @@QueryUserRoleID = 1 OR @QueryUserID = @UserID
    	BEGIN
    		SELECT 	TT_Projects.ProjectID,
    			Name,
    			Description,
    			ManagerUserID,
    			EstCompletionDate,
    			EstDuration
    		FROM TT_Projects
    		INNER JOIN TT_ProjectMembers ON TT_ProjectMembers.ProjectID = TT_Projects.ProjectID
    		WHERE UserID = @UserID
    	END

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.