# "ListProjects" Stored Procedure

Description:

This stored procedure list project information based on the role of the requesing user. If the user is in an Administrator role (RoleID=1), they are shown their projects only; if the user is in a Project Manager role (RoleID=2), they are shown all of the projects that they manage.

Definition:

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

    	IF @RoleID = 1
    	BEGIN
    		SELECT ProjectID,
    			Name as ProjectName,
    			Description,
    			ManagerUserID,
    			UserName,
    			EstCompletionDate,
    			EstDuration
    		FROM
    			TT_Projects
    		INNER JOIN
    			TT_Users
    		ON
    			ManagerUserID = UserID
    	END

    	ELSE IF @RoleID = 2
    	BEGIN
    		SELECT ProjectID,
    			Name as ProjectName,
    			Description,
    			ManagerUserID,
    			UserName,
    			EstCompletionDate,
    			EstDuration
    		FROM
    			TT_Projects
    		INNER JOIN
    			TT_Users
    		ON
    			ManagerUserID = UserID
    		WHERE ManagerUserID = @UserID
    	END

Database Tables Used:

The primary key in the Projects table is the ProjectID identity field. The ManagerUserID field references the UserID field in the Users table.

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