# "AddProject" 存储过程

说明：

这个存储过程在插入成员信息和种类信息的同时也插入了一个项目。这个存储过程封装在SQL Server事务过程中，因此如果在数据插入的过程中，有任何一个数据没有插入成功的话，那么所有状态回滚到初始的状态。

定义：

    	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

用到的数据库表：

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

Categories表的主键是CategoryID，表结构如下图所示：

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

ProjectMembers表和EntryLog表的外部键是ProjectID 和UserID，通过这两个字段把这两个表连接起来。ProjectMembers表和Projects表的外部键是ProjectID。通过ProjectID 把ProjectMembers表和Projects表连接起来了。