Stored Programs Error Handling Exercise


In this exercise you will add an error handler to the stored procedure created in exercise 2.

Tasks:

Declare an error handler for the first stored procedure below. The error handler should follow these specs:

delimiter $$
DROP PROCEDURE IF EXISTS sp_ModifyName$$
CREATE PROCEDURE sp_ModifyName (IN currentName VARCHAR(40), IN newName VARCHAR(40))
BEGIN
DECLARE cID VARCHAR(5);
SET cID = (SELECT CustomerID from customers where CompanyName = currentName);
UPDATE customers SET CompanyName = newName WHERE CustomerID = cID;
END$$
delimiter ;

delimiter //
DROP PROCEDURE IF EXISTS sp_ModifyName//
CREATE PROCEDURE modifyName (IN currentLName VARCHAR(25), IN newLName VARCHAR(25))
BEGIN
DECLARE cNum INT;
SET cNum = (SELECT customerNum from customer where customerLastName = currentLName);
UPDATE customer SET customerLastName = newLName WHERE customerNum = cNum;
END//
delimiter ;

This reference is very useful.

Solution