Question

Step 2: Create Stored Procedures to Add/Update/Delete an entity table

Create a script to create a table named ProjectBilling which will have the following columns:

• projectBillID char(6) : A 6 character unique identifier (numbers and letters)

• TransAmount decimal(16,9) : The amount of the transaction

• TransDesc varchar(255): A description of the transaction

• TransDate datetime: The date of the transaction

• projectID char(4):The Id of the project

• accountMgr char(8):The employee who manages the bill ledger Include this create table script in your script.

You will create the SQL Scripts to create procedures to insert/ update data. The following definitions specify the parameters that can be passed in.

The underlined parameters are required, but the other parameters will be optional.

Make sure that your procedure inserts records if the required parameters do not exist, but they update records if the required parameters do exist.

For example: If SP_AddUpdateProjBill: passes in projectBillID “AA0102” and it DOESN’T exists in the projectbilling table(s) , it will insert the values passed in.

If SP_AddUpdateProjectBill: passes in projectBillID “AA0102” and it DOES exists in the projectbilling table(s) , it will UPDATE the values passed in for the AA0102 record.

Procedures Needed: - SP_AddUpdateProjectBill: Adds/Updates the ProjectBilling Table with all the field information.

o Parameters: projectBillID, TransAmount, TransDesc, TransDate, projectId, accountMgr. -

SP_DeleteProjectBill: Deletes a project bill by the ProjectBill Id.

o Parameters: projectBillId

Answer 1

Below is the code to create a table:

CREATE TABLE ProjectBilling

( projectBillID char(6) primary key,

TransAmount decimal(16,9),

TransDesc varchar(255),

TransDate datetime,

projectID char(4),

accountMgr char(8) )

Now let's see the procedure to insert/update table:

CREATE PROCEDURE SP_AddUpdateProjectBill (@projectBillID char(6), @TransAmount decimal(16,9), @TransDesc varchar(255), @TransDate datetime, @projectID char(4), @accountMgr char(8) )

AS
BEGIN


IF EXISTS (Select * from ProjectBilling where projectBillID=@projectBillID

                               and TransAmount = @TransAmount

                               and TransDesc = @TransDesc

                               and TransDate = @TransDate

                               and projectID = @projectID

                               and accountMgr = @accountMgr )

BEGIN

      UPDATE ProjectBilling

      SET

          TransAmount = @TransAmount

          TransDesc = @TransDesc

          TransDate = @TransDate

          projectID = @projectID

          accountMgr = @accountMgr

       WHERE where projectBillID=@projectBillID

END
END

ELSE

BEGIN

      INSERT INTO ProjectBilling

      VALUES (@projectBillID, @TransAmount, @TransDesc, @TransDate, @projectID, @accountMgr )

END

END

Now let us see the stored procedure to delete from the table:

CREATE PROCEDURE SP_DeleteProjectBill ( @projectBillId char(6) ) 
AS  
BEGIN  
DELETE FROM  ProjectBilling WHERE  projectBillId= @projectBillId
END