<html>
<head>
<link rel="stylesheet" href="style.css">
</head>
<body>
<h1>
"AddProject" Stored Procedure
</h1>
<b>Description:</b>
<p>
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.
</p>
<b>Definition:</b>
<pre>
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
</pre>
<b>Database Tables Used: </b>
<p>
The primary key in the Projects table is the ProjectID identity field.
<p>
<img src="../docs/images/1x1.gif" width="25"> <img src="../docs/images/Projects.png"></p>
<p>
The primary key in the Categories table is the CategoryID identity field.
<p>
<img src="../docs/images/1x1.gif" width="25"> <img src="../docs/images/Categories.png"></p>
<p>
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.
<p>
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.
<p>
<img src="../docs/images/1x1.gif" width="25"> <img src="../docs/images/ProjectMembers.png"></p>
</body>
</html>
Submit an article or tip