Stored Procedures Exercise

In this exercise you will create a stored procedure that contains a transaction.

Before developing the sProc, you must modify your Northwind database.

The Save-a-lot Markets in Boise is being hit hard by inflation, so they decided to change their name to Save-a-Bit Markets.

The transaction should update the information for CompanyName in the customers table.

Pass BOTH the current company name and the new company name to the sProc as parameters.
Declare a local variable called cID.
Declare an error handler.
The first query in the transaction should be a SELECT statement that returns the CustomerID for the current company name and assigns that CustomerID to cID. You will need parentheses around the SELECT query on the right side of the SET statement.
The next query should update the record for that CustomerID, changing the current company name to the new company name.
Be sure to save the transaction as a stored procedure and test it.

Testing:

Test the sProc with a statement like the following:

CALL modifyName ('Save-a-lot Markets', 'Save-a-Bit Markets');

In this exercise you will create a stored procedure that contains a transaction.

Before developing the sProc, you must modify your Wind Riders of the Lost River Range database. First add a customer named Andrea Jamison to your Customer table.

You will next design a transaction to handle the following situation. Andrea Jamison gets married and decides to adopt her husband's surname – Chatterton.

Recall that your Customer table has the following fields:

Customer (CustomerNum, CustomerName, CustomerAddress, CustomerCity, CustomerState, CustomerZip, CustomerHomePhone, CustomerCellPhone, CustomerWorkPhone, CustomerEmail)

The original information for Andrea follows:

customerNum
customerLastName	Jamison
customerFirstName	Andrea
customerAddress	3610 N. Bitteroot Drive
CustomerCity	Coeur d'Alene
customerState	ID
customerZip	83815
customerCellPhone	(208) 471-1882
customerEmail	an_jam@gmail.com

Notice that the customerNum value has been left blank.

The customerNum field is supposed to be an auto_increment field, but you may have to modify the table structure to establish that.
No value was specified for the AUTO_INCREMENT column, so MySQL will assign sequence numbers automatically
You may need to explicitly assign NULL or 0 to the column to generate sequence numbers.

The transaction should update the information for last name in the customer table.

Pass BOTH the current customer last name and the new customer last name to the sProc as parameters.
Declare a local variable called cNum.
Declare an error handler.
The first query in the transaction should be a SELECT statement that returns the customerNum for the current name and assigns that customerNum to cNum. You will need parentheses around the SELECT query on the right side of the SET statement.
The next query should update the record for that customer number, changing the current customer last name to the new customer last name..
Be sure to save the transaction as a stored procedure and test it.

Testing:

Test the sProc with a statement like the following:

CALL modifyName ('Jamison', 'Chatterton');

In this exercise you will create a stored procedure that contains a transaction for the Northwind database.

Design a transaction to handle the following situation: the owner of Northwind has decided that she no longer wishes to do business with Supplier 18, Aux Joyeux Ecclésiastiques, because they have a funny name. Instead, she wishes to replace them with Wee Willie's Exotic (Cooking) Oils.

The information for Wee Willie's follows:

supplierID
CompanyName	Wee Willie's Exotic (Cooking) Oils
ContactName	Willie Brown
ContactTitle	Owner/Sales Manager
Address	1412 Post Dr.
City	Inkom
Region	ID
PostalCode	83245
Country	USA
Phone	(208) 775-4462
Fax	(208) 775-4463
HomePage

The transaction should shift business from one supplier to another.

Add the information for Wee Willie's to the Supplier table.
Find the SupplierID assigned to Wee Willie and assign it to a local variable.
Update the Products table, replacing all references to SupplierID 18 with Wee Willie's SupplierID.
Remove SupplierID 18 from the Supplier table.
Include appropriate error handling.
Save the transaction as a stored procedure.

Testing:

Test to verify that the new supplier has been entered in the suppliers table with the following nested query:

SELECT SupplierID, CompanyName FROM suppliers WHERE SupplierID = (SELECT MAX(SupplierID) FROM suppliers);

Test to verify that the new supplier has been assigned to all of Supplier 18, Aux Joyeux Ecclésiastiques accounts in the products table with the following nested query:

SELECT ProductID, ProductName from products WHERE SupplierID = (SELECT MAX(SupplierID) FROM products);

Solution