# "AddProject" Stored Procedure

Description:

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

Definition:

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

    		DECLARE @Error int
    		DECLARE @ProjectID int
    		DECLARE @TempString varchar(4000)
    		DECLARE @Temp nvarchar(4000)
    		DECLARE @Count int
    		DECLARE @TempTable TABLE(UserID int PRIMARY KEY)
    		DECLARE @InnerTemp nvarchar(50)
    		DECLARE @CatName varchar(20)
    		DECLARE @Abbrev varchar(5)
    		DECLARE @Duration decimal(10,2)
    		DECLARE @InnerCount int

    		BEGIN TRANSACTION

    		INSERT INTO TT_Projects
    		(
    			[Name],
    			[Description],
    			ManagerUserID,
    			EstCompletionDate,
    			EstDuration,
    			CreationDate
    		)
    		VALUES
    		(
    			@Name,
    			@Description,
    			@ManagerUserID,
    			@EstCompletionDate,
    			@EstDuration,
    			getdate()
    		)

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

    		SET @ProjectID = @@Identity

    		SET @TempString = @Members

    		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))

    		INSERT INTO TT_ProjectMembers
    			SELECT @ProjectID, UserID FROM @TempTable

    		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 @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)

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

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

    			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 @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)

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

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

    		COMMIT TRANSACTION

    		SELECT @ProjectID AS ProjectID

    	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.

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 EntryLog table including both the ProjectID and UserID fields; there is also a Foreign key relationship between this table and the Projects table on the ProjectID field.