Write a stored procedure that inserts a new row

Assignment Help Computer Engineering

Reference no: EM132214036

Write a stored procedure that inserts a new row into the Supplier table and then adds at least one new product that the new supplier stocks into the Products table.

This program must include the ability to handle a failed transaction in which case it should issue an error message and a rollback.

Note:

The ProductID in the Products table is auto-generated (Identity Specification is set to Yes). This means that Northwind only gets a certain product from a single Supplier.

In other words, Chai is only gotten from SupplierID = 1 which is Exotic Liquids.

Answer

A partial solution:

CREATE PROCEDURE AddSupplierProduct01

--The first set of columns information is for the Supplier table...

--This information is for the Product table...

BEGIN TRANSACTION

INSERT Suppliers (CompanyName, ContactName, Address, City, Region,PostalCode, Country, Phone)

VALUES (@CompanyName, @ContactName, @Address, @City, @Region, @PostalCode, @Country, @Phone)

IF @@error <> 0

BEGIN

ROLLBACK TRAN

RETURN

END

DECLARE @InsertSupplierID int

SELECT @InsertSupplierID=@@identity

INSERT Products (ProductName, SupplierID, CategoryID, QuantityPerUnit, Discontinued)

VALUES (@ProductName, @InsertSupplierID, @CategoryID, @QuantityPerUnit, @Discontinued)

IF @@error <> 0

BEGIN

ROLLBACK TRAN

RETURN

END

COMMIT TRANSACTION

Another partial solution:

BEGIN TRY

BEGIN TRAN;

INSERT Suppliers

VALUES(@CompanyName, @ContactName, @ContactTitle, @Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax, @HomePage)

DECLARE @SupplierID int

SELECT @SupplierID = SCOPE_IDENTITY()

INSERT Products

VALUES(@ProductName, @SupplierID, @CategoryID, @QuantityPerUnit, @UnitPrice, @UnitsInStock, @UnitsOnOrder, @ReorderLevel, @Discontinued)

COMMIT TRAN;

END TRY

BEGIN CATCH

ROLLBACK TRAN;

Print 'An error occured and the row was not added. Please try again.'

Reference no: EM132214036

Questions Cloud

Write a statistics class to compute mean and variance : Write a statistics class to compute mean, variance, max and min of a vector of numbers.

Take an input argument of r0 holding the address : Write a subroutine called TOLOWER. This takes an input argument of R0 holding the address of aproperly terminated string.

Evaluate the organisational structure of the workplace : Analyse the Internal, External and Industry Environment in which the work placement operates - Evaluate the organisational structure of the workplace

Write a static method with one integer parameter : Write a static method called Result with one integer parameter x that returns the value at polynomial 3x - 7.

Write a stored procedure that inserts a new row : Write a stored procedure that inserts a new row into the Supplier table and then adds at least one new product that the new supplier stocks.

Compute the checksum for a data table : Write a subroutine that computes the CHECKSUM for a data table (by XO Ring all the data bytes).

Find the cyclic rotation that is the smallest lexicographic : Write a SuffixArray client that, given a string, finds the cyclic rotation that is the smallest lexicographically in linear time.

Write and test a data structures template : Write the template for an array of capacity =2 (default constructor) of unspecified type.

Calculate the number of elements in an array not counting : MSP430G2553 :Subroutine expects the Input on the stack as shown above: The input is the starting address of an array consisting of word length elements.

User Account

All Pages