# "ListProjectsByIDs" 存储过程

说明：

这个过程能够从一串ProjectID 中得到一连串的项目摘要信息。但这些返回的摘要将根据用户所提供UserID来作进一步的细化。

定义：

    	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

用到的数据库表：

Projects表的主键是ProjectID字段，它的表结构如下图所示：

EntryLog表中的主键是EntryLogID字段，它的表结构如下图所示，其中 ProjectID字段和Projects表中的主键外部相关。