# "UpdateProject" Stored Procedure

Description:

This stored procedure updates a project along with its members and categories. This is wrapped in a SQL Server Transaction, so that if any of the updates fail, everything rolls back.

Definition:

    	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

Database Tables Used:

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

The primary key in the Categories table is the CategoryID identity field. There is a Foreign key relationship between this table and the Projects table on the ProjectID 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.