---------------------------------------------------------------------------------

-- Stored procedure that will insert 1 row in the table 'Assignment'

-- Gets: @iPersonID int

-- Gets: @iDepartmentID int

-- Gets: @iPriorityID int

-- Gets: @siPriorityRank smallint

-- Gets: @iRequestID int

-- Gets: @siNumber smallint

-- Gets: @iProjectID int

-- Returns: @iAssignmentID int

-- Returns: @iErrorCode int

---------------------------------------------------------------------------------

ALTER PROCEDURE dbo.sp_Assignment_Insert

@iPersonID int,

@iDepartmentID int,

@iPriorityID int,

@siPriorityRank smallint,

@iRequestID int,

@siNumber smallint,

@iProjectID int,

@iAssignmentID int OUTPUT,

@iErrorCode int OUTPUT

AS

SET NOCOUNT ON

-- INSERT a new row in the table.

INSERT [dbo].[Assignment]

(

[PersonID],

[DepartmentID],

[PriorityID],

[PriorityRank],

[RequestID],

[Number],

[ProjectID]

)

VALUES

(

@iPersonID,

@iDepartmentID,

@iPriorityID,

@siPriorityRank,

@iRequestID,

@siNumber,

@iProjectID

)

-- Get the Error Code for the statement just executed.

SELECT @iErrorCode=@@ERROR

-- Get the IDENTITY value for the row just inserted.

SELECT @iAssignmentID=SCOPE_IDENTITY()

-- Stored procedure that will delete an existing row from the table 'Assignment'

-- using the Primary Key.

-- Gets: @iAssignmentID int

-- Returns: @iErrorCode int

---------------------------------------------------------------------------------

ALTER PROCEDURE dbo.sp_Assignment_Delete

@iAssignmentID int,

@iErrorCode int OUTPUT

AS

SET NOCOUNT ON

-- DELETE an existing row from the table.

DELETE FROM [dbo].[Assignment]

WHERE

[AssignmentID] = @iAssignmentID

-- Get the Error Code for the statement just executed.

SELECT @iErrorCode=@@ERROR

-- Stored procedure that will update an existing row in the table 'Assignment'

-- Gets: @iAssignmentID int

-- Gets: @iPersonID int

-- Gets: @iDepartmentID int

-- Gets: @iPriorityID int

-- Gets: @siPriorityRank smallint

-- Gets: @iRequestID int

-- Gets: @siNumber smallint

-- Gets: @iProjectID int

-- Returns: @iErrorCode int

---------------------------------------------------------------------------------

ALTER PROCEDURE dbo.sp_Assignment_Update

@iAssignmentID int,

@iPersonID int,

@iDepartmentID int,

@iPriorityID int,

@siPriorityRank smallint,

@iRequestID int,

@siNumber smallint,

@iProjectID int,

@iErrorCode int OUTPUT

AS

SET NOCOUNT ON

-- UPDATE an existing row in the table.

UPDATE [dbo].[Assignment]

SET

[PersonID] = @iPersonID,

[DepartmentID] = @iDepartmentID,

[PriorityID] = @iPriorityID,

[PriorityRank] = @siPriorityRank,

[RequestID] = @iRequestID,

[Number] = @siNumber,

[ProjectID] = @iProjectID

WHERE

[AssignmentID] = @iAssignmentID

-- Get the Error Code for the statement just executed.

SELECT @iErrorCode=@@ERROR

-- Stored procedure that will select all rows from the table 'Assignment'

-- Returns: @iErrorCode int

---------------------------------------------------------------------------------

ALTER PROCEDURE dbo.sp_Assignment_SelectAll

@iErrorCode int OUTPUT

AS

SET NOCOUNT ON

-- SELECT all rows from the table.

SELECT

[AssignmentID],

[PersonID],

[DepartmentID],

[PriorityID],

[PriorityRank],

[RequestID],

[Number],

[ProjectID]

FROM [dbo].[Assignment]

ORDER BY

[AssignmentID] ASC

-- Get the Error Code for the statement just executed.

SELECT @iErrorCode=@@ERROR

-- Stored procedure that will select an existing row from the table 'Assignment'

-- based on the Primary Key.

-- Gets: @iAssignmentID int

-- Returns: @iErrorCode int

---------------------------------------------------------------------------------

ALTER PROCEDURE dbo.sp_Assignment_SelectOne

@iAssignmentID int,

@iErrorCode int OUTPUT

AS

SET NOCOUNT ON

-- SELECT an existing row from the table.

SELECT

[AssignmentID],

[PersonID],

[DepartmentID],

[PriorityID],

[PriorityRank],

[RequestID],

[Number],

[ProjectID]

FROM [dbo].[Assignment]

WHERE

[AssignmentID] = @iAssignmentID

-- Get the Error Code for the statement just executed.

SELECT @iErrorCode=@@ERROR

-- Stored procedure that will select one or more existing rows from the table 'Assignment'

-- based on a foreign key field.

-- Gets: @iProjectID int

-- Returns: @iErrorCode int

---------------------------------------------------------------------------------

ALTER PROCEDURE dbo.sp_Assignment_SelectAllWProjectIDLogic

@iProjectID int,

@iErrorCode int OUTPUT

AS

SET NOCOUNT ON

-- SELECT one or more existing rows from the table.

SELECT

[AssignmentID],

[PersonID],

[DepartmentID],

[PriorityID],

[PriorityRank],

[RequestID],

[Number],

[ProjectID]

FROM [dbo].[Assignment]

WHERE

[ProjectID] = @iProjectID

-- Get the Error Code for the statement just executed.

SELECT @iErrorCode=@@ERROR