# "UpdateProject" 存储过程

说明：

这个过程会随着项目成员及项目类别的变化不断更新项目信息。这个存储过程封装在SQL Server事务过程中，更新过程中任何一步没能更新成功，那么所有状态回滚到初始的状态。

定义：

    	CREATE PROCEDURE TT_UpdateProject
    	(
    		@ProjectID int,
    		@Name nvarchar(50),
    		@Description nvarchar(1024),
    		@ManagerUserID int,
    		@EstCompletionDate datetime,
    		@EstDuration int,
    		@SelectedMembers nvarchar(2000),
    		@Categories nvarchar(4000)
    	)
    	AS

    		DECLARE @Error int
    		DECLARE @TempTable TABLE(UserID int PRIMARY KEY)
    		DECLARE @TempString varchar(2000)
    		DECLARE @Temp varchar(4000)
    		DECLARE @Count int
    		DECLARE @TempCatTable TABLE(CategoryID int primary key)
    		DECLARE @InnerTemp nvarchar(50)
    		DECLARE @CatID int
    		DECLARE @CatName varchar(20)
    		DECLARE @Abbrev varchar(5)
    		DECLARE @Duration int
    		DECLARE @InnerCount int

    		BEGIN TRANSACTION

    		-- Update the Project
    		UPDATE
    			TT_Projects
    		SET
    			Name=@Name,
    	        		Description = @Description,
    			ManagerUserID = @ManagerUserID,
    			EstCompletionDate = @EstCompletionDate,
    			EstDuration = @EstDuration

    		WHERE
    			ProjectID = @ProjectID

    		SET @Error = @@ERROR
    		IF @Error != 0 GOTO ERROR_HANDLER

    		SET @TempString = @SelectedMembers

    		SET @Count = CHARINDEX(',', @TempString)

    		WHILE @Count > 0
    		BEGIN
    			SET @Temp = SUBSTRING(@TempString, 1, (@Count - 1))
    			INSERT INTO @TempTable VALUES(CAST(@Temp AS int))
    			SET @TempString = SUBSTRING(@TempString, (@Count + 1), (LEN(@TempString) - @Count))
    			SET @Count = CHARINDEX(',', @TempString)
    		END

    		INSERT INTO @TempTable VALUES(CAST(@TempString AS int))

    		DELETE
    			TT_ProjectMembers
    		WHERE
    			ProjectID = @ProjectID
    		AND UserID NOT IN(SELECT UserID FROM @TempTable)

    		INSERT INTO TT_ProjectMembers
    			SELECT @ProjectID, UserID FROM @TempTable WHERE UserID NOT IN
    				(SELECT UserID FROM TT_ProjectMembers WHERE ProjectID = @ProjectID)

    		SET @Error = @@ERROR
    		IF @Error != 0 GOTO ERROR_HANDLER

    		SET @TempString = @Categories

    		SET @Count = CHARINDEX(';', @TempString)
    		WHILE @Count > 0
    		BEGIN
    			SET @Temp = SUBSTRING(@TempString, 1, (@Count - 1))

    			SET @InnerCount = CHARINDEX(',', @temp)

    			SET @InnerTemp = SUBSTRING(@temp, 1, (@InnerCount - 1))
    			SET @CatID = @InnerTemp

    			SET @temp = SUBSTRING(@temp, (@InnerCount + 1), (LEN(@temp) - @InnerCount))
    			SET @InnerCount = CHARINDEX(',', @temp)

    			SET @InnerTemp = SUBSTRING(@temp, 1, (@InnerCount - 1))
    			SET @CatName = @InnerTemp

    			SET @temp = SUBSTRING(@temp, (@InnerCount + 1), (LEN(@temp) - @InnerCount))
    			SET @InnerCount = CHARINDEX(',', @temp)

    			SET @InnerTemp = SUBSTRING(@temp, 1, (@InnerCount - 1))
    			SET @ABbrev = @InnerTemp

    			SET @temp = SUBSTRING(@temp, (@InnerCount + 1), (LEN(@temp) - @InnerCount))
    			SET @InnerCount = CHARINDEX(',', @temp)

     			SET @duration =  CAST(@temp AS int)

    			IF not exists(SELECT CategoryID from TT_categories WHERE ProjectID = @projectID AND CategoryID = @catID)
    			BEGIN

    				INSERT INTO TT_Categories
    				(
    					ProjectID,
    					[Name],
    					Abbreviation,
    					EstDuration
    				)
    				VALUES
    				(
    					@ProjectID,
    					@CatName,
    					@Abbrev,
    					@Duration
    				)

    				SET @Error = @@ERROR
    				IF @Error != 0 GOTO ERROR_HANDLER

    				SELECT @CatID = @@IDENTITY

    			END
    			ELSE
    			BEGIN
    				UPDATE
    					TT_Categories
    				SET
    					[Name] = @CatName,
    					Abbreviation = @Abbrev,
    					EstDuration = @Duration
    				WHERE
    					ProjectID = @projectID
    				AND
    					CategoryID = @catID

    				SET @Error = @@ERROR
    				IF @Error != 0 GOTO ERROR_HANDLER
    			END

    			INSERT INTO @TempCatTable VALUES(@CatID)

    			SET @TempString = SUBSTRING(@TempString, (@Count + 1), (LEN(@TempString) - @Count))
    			SET @Count = CHARINDEX(';', @TempString)
    		END

    		set @temp =  @tempstring
    		SET @InnerCount = CHARINDEX(',', @temp)

    		SET @InnerTemp = SUBSTRING(@temp, 1, (@InnerCount - 1))
    		SET @CatID = @InnerTemp

    		SET @temp = SUBSTRING(@temp, (@InnerCount + 1), (LEN(@temp) - @InnerCount))
    		SET @InnerCount = CHARINDEX(',', @temp)

    		SET @InnerTemp = SUBSTRING(@temp, 1, (@InnerCount - 1))
    		SET @CatName = @InnerTemp

    		SET @temp = SUBSTRING(@temp, (@InnerCount + 1), (LEN(@temp) - @InnerCount))
    		SET @InnerCount = CHARINDEX(',', @temp)

    		SET @InnerTemp = SUBSTRING(@temp, 1, (@InnerCount - 1))
    		SET @Abbrev = @InnerTemp

    		SET @temp = SUBSTRING(@temp, (@InnerCount + 1), (LEN(@temp) - @InnerCount))
    		SET @InnerCount = CHARINDEX(',', @temp)

    		SET @duration =  CAST(@temp AS int)

    			IF not exists(SELECT CategoryID from TT_categories WHERE ProjectID = @projectID AND CategoryID = @catID)
    			BEGIN
    				INSERT INTO TT_Categories
    				(
    					ProjectID,
    					[Name],
    					Abbreviation,
    					EstDuration
    				)
    				VALUES
    				(
    					@ProjectID,
    					@CatName,
    					@Abbrev,
    					@Duration
    				)

    				SET @Error = @@ERROR
    				IF @Error != 0 GOTO ERROR_HANDLER

    				SELECT @CatID = @@IDENTITY

    			END
    			ELSE
    			BEGIN
    				UPDATE
    					TT_Categories
    				SET
    					[Name] = @CatName,
    					Abbreviation = @Abbrev,
    					EstDuration = @Duration
    				WHERE
    					ProjectID = @projectID
    				AND
    					CategoryID = @catID

    				SET @Error = @@ERROR
    				IF @Error != 0 GOTO ERROR_HANDLER
    			END

    			INSERT INTO @TempCatTable VALUES(@CatID)

    		DELETE
    			TT_Categories
    		WHERE
    			ProjectID = @ProjectID
    		AND
    			CategoryID NOT IN(SELECT CategoryID FROM @TempCatTable)

    		SET @Error = @@ERROR
    		IF @Error != 0 GOTO ERROR_HANDLER

    		COMMIT TRANSACTION
    		RETURN 0

    	ERROR_HANDLER:
    		IF @@TRANCOUNT != 0 ROLLBACK TRANSACTION
    		RETURN @Error

用到的数据库表：

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

Categories表的主键是CategoryID字段。Categories表和Projects表通过ProjectID字段进行外部关联。Categories表结构如下图所示：

ProjectMembers表的主键是一个复合的主键，它由ProjectID字段和UserID字段共同组成。这个表描述的是用户在项目中的所具有的成员资格等。用户只需要输入他们成为项目成员的开始时间。

ProjectMembers表和Projects 表通过ProjectID字段进行外部关联。