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...
AS
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.'