# "ListProjectsByIDs" Stored Procedure

Description:

This stored procedure retrieves a list of Project summary information from within a list of possible Project IDs. The records returned are further narrowed based on the role of the User ID submitted.

Definition:

    	CREATE  PROCEDURE TT_ListProjectsByIDs
    	(
    		@ProjectIDs nvarchar(512),
    		@UserID int
    	)
    	AS

    		DECLARE @sql nvarchar(1024),
    				@RoleID int

    		SELECT @RoleID = RoleID
    		FROM TT_Users
    		WHERE UserID = @UserID;

    		IF (@RoleID = 1)
    			BEGIN
    			SET @sql = 'SELECT P.ProjectID,'
    			SET @sql = @sql + ' P.Name AS ProjectName,'
    			SET @sql = @sql + ' P.EstCompletionDate,'
    			SET @sql = @sql + ' P.EstDuration AS EstHours,'
    			SET @sql = @sql + ' Sum(EL.Duration) AS ActualHours '
    			SET @sql = @sql + 'FROM TT_Projects P INNER JOIN TT_EntryLog EL'
    			SET @sql = @sql + ' ON P.ProjectID = EL.ProjectID '
    			SET @sql = @sql + 'WHERE P.ProjectID IN (' + @ProjectIDs + ') '
    			SET @sql = @sql + 'GROUP BY P.ProjectID, P.Name, P.EstCompletionDate, P.EstDuration'
    			END
    		ELSE IF (@RoleID = 2)
    			BEGIN
    			SET @sql = 'SELECT P.ProjectID,'
    			SET @sql = @sql + ' P.Name AS ProjectName,'
    			SET @sql = @sql + ' P.EstCompletionDate,'
    			SET @sql = @sql + ' P.EstDuration AS EstHours,'
    			SET @sql = @sql + ' Sum(EL.Duration) AS ActualHours '
    			SET @sql = @sql + 'FROM TT_Projects P INNER JOIN TT_EntryLog EL'
    			SET @sql = @sql + ' ON P.ProjectID = EL.ProjectID '
    			SET @sql = @sql + 'WHERE P.ProjectID IN (' + @ProjectIDs + ') '
    			SET @sql = @sql + ' AND P.ManagerUserID = ' + CAST(@UserID AS nvarchar(20))
    			SET @sql = @sql + 'GROUP BY P.ProjectID, P.Name, P.EstCompletionDate, P.EstDuration'
    			END
    		ELSE
    			BEGIN
    			SET @sql = 'SELECT P.ProjectID,'
    			SET @sql = @sql + ' P.Name AS ProjectName,'
    			SET @sql = @sql + ' P.EstCompletionDate,'
    			SET @sql = @sql + ' P.EstDuration AS EstHours,'
    			SET @sql = @sql + ' P.EstDuration AS ActualHours '
    			SET @sql = @sql + 'FROM TT_Projects P WHERE 1=0 '
    			END

    		EXEC sp_executesql @sql

Database Tables Used:

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

The primary key in the EntryLog table is the EntryLogID identity field. The field ProjectID references back to the Projects table's primary key.