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.

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.' END CATCH;