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