Solution: Stored Procedures Exercise 2


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

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

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

Solution:

delimiter $$
DROP PROCEDURE IF EXISTS sp_ModifyName$$
CREATE PROCEDURE sp_ModifyName (IN currentName VARCHAR(40), IN newName VARCHAR(40))
BEGIN
-- Declare a local variable called cID.
DECLARE cID VARCHAR(5);

-- Include a SELECT statement that returns the CustomerID for the current company name and assigns that CustomerID to cID.
SET cID = (SELECT CustomerID from customers where CompanyName = currentName);

-- Update the record for that CustomerID, changing the current company name to the new company name.
UPDATE customers SET CompanyName = newName WHERE CustomerID = cID;
END$$
delimiter ;

Testing:

Test the sProc with a statement like the following:

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

Verify that the company name changed with the following query:

SELECT CompanyName from customers WHERE CustomerID = "SAVEA";

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

Solution:

delimiter $$
DROP PROCEDURE IF EXISTS modifyName$$
CREATE PROCEDURE modifyName (IN currentLName VARCHAR(25), IN newLName VARCHAR(25))
BEGIN
-- Declare a local variable called cNum.
DECLARE cNum INT;

-- Include a SELECT statement that returns the customerNum for the current name and assigns that customerNum to cNum.
SET cNum = (SELECT customerNum from customer where customerLastName = currentLName);

-- Update the record for that customer number, changing the current customer last name to the new customer last name.
UPDATE customer SET customerLastName = newLName WHERE customerNum = cNum;
END$$
delimiter ;

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.

Solution:

DELIMITER $$
CREATE PROCEDURE sp_ChangeSupplier()
BEGIN
DECLARE nextID INT;
SET foreign_key_checks = 0;

-- Add the information for Wee Willie's to the Supplier table.
INSERT INTO suppliers(SupplierID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax, HomePage)
VALUES (NULL, "Wee Willie‘s Exotic (Cooking) Oils", "Willie Brown", "Owner/Sales Manager", "1412 Post Dr.", "Inkom", "ID", "83245", "USA", "(208) 775-4462", "(208) 775-4463", NULL);

-- Find the SupplierID assigned to Wee Willie and assign it to a local variable.
SET nextID = LAST_INSERT_ID();

-- Update the Products table, replacing all references to SupplierID 18 with Wee Willie's SupplierID.
UPDATE products SET SupplierID = nextID WHERE SupplierID = 18;

-- Remove SupplierID 18 from the Supplier table.
DELETE FROM suppliers WHERE SupplierID = 18;
END$$
DELIMITER ;

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);