Solution: sProcs Error Handling Exercise

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

Tasks:

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

• handler_action: EXIT
• or condition_name: NOT FOUND
• or condition_name: SQLWARNING
• or condition_name: SQLEXCEPTION

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 ;

Solution:

delimiter $$
DROP PROCEDURE IF EXISTS sp_ModifyName $$
CREATE PROCEDURE sp_ModifyName (IN currentName VARCHAR(40), IN newName VARCHAR(40))
BEGIN
DECLARE cID VARCHAR(5);

DECLARE EXIT HANDLER FOR NOT FOUND, SQLWARNING, SQLEXCEPTION ROLLBACK;
BEGIN
GET DIAGNOSTICS CONDITION 1
@sqlstate = RETURNED_SQLSTATE,
@errno = MYSQL_ERRNO,
@text = MESSAGE_TEXT;
SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
SELECT @full_error;
ROLLBACK;
END;

SET cID = (SELECT CustomerID from customers where CompanyName = currentName);
UPDATE customers SET CompanyName = newName WHERE CustomerID = cID;
IF (ROW_COUNT() = 0) THEN
-- To signal a generic SQLSTATE value, use '45000', which means "unhandled user-defined exception."
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Problem encountered - no customers were updated.';
END IF;
END$$
delimiter ;

Testing:

The following query should fail:

CALL sp_ModifyName ('Save-a-Bite Markets', 'Save-Nada Markets');

Notes:

If an error is thrown by a procedure, DECLARE ... HANDLER will prevent the error from reaching the client. If you want the client to see an error, there are three possibilities:

• Don't declare any error handlers and write the procedure code in such a way that MySQL will naturally generate an error when a customer is not found
• Don't declare any error handlers and use SIGNAL to purposely generate an error message when a customer is not found
• Declare an error handler and use RESIGNAL in the error handler to throw the original error message back to the client (after processing the error handler code)

Note that you don't need to show an error in the client to determine whether or not the procedure worked. Other options:

• Handle errors in the procedure and ensure the ending ROW_COUNT() after the procedure runs reflects the number of rows updated by the procedure, then make your application look at the ROW_COUNT() to determine if anyone's name changed
• Handle errors in the procedure and add an OUT parameter that explicitly returns the number of rows affected
• Handle errors in the procedure and add an OUT parameter that returns true/false depending on whether the procedure made its way to the end without encountering the error handler

Here are some options for the sp_modifyName procedure:

1. This will not generate any errors, but it will return the number of rows affected. This would allow the application to check how many customers had their names changed. If 0 rows were affected, nobody matched the old name. Remember than an UPDATE that affects 0 rows is not an error. Consider this:

   delimiter $$
   CREATE PROCEDURE sp_ModifyName1 (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$$

2. If you want to generate an error that doesn't occur naturally, use SIGNAL and don't declare an error handler. Consider this:

   delimiter $$
   CREATE PROCEDURE sp_ModifyName2 (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;
   IF (ROW_COUNT() = 0) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'No customers were updated.'; END IF;
   END$$

3. If you require a transaction for your procedure, then you could use a multi-line error handler that would both rollback the transaction and generate an error for the client. The RESIGNAL statement allows an error that was caught in the error handler to be thrown again so the client sees the error message. Consider the procedure below, and see http://guyharrison.squarespace.com/blog/2009/7/13/signal-and-resignal-in-mysql-54-and-60.html for another example of RESIGNAL.

   delimiter $$
   CREATE PROCEDURE sp_ModifyName3 (IN currentName VARCHAR(40), IN newName VARCHAR(40))
   BEGIN
   DECLARE cID VARCHAR(5);
   
   DECLARE EXIT HANDLER FOR NOT FOUND, SQLWARNING, SQLEXCEPTION
   BEGIN
   ROLLBACK;
   RESIGNAL;
   END;
   
   START TRANSACTION;
   SET cID = (SELECT CustomerID from customers where CompanyName = currentName);
   UPDATE customers SET CompanyName = newName WHERE CustomerID = cID;
   IF (ROW_COUNT() = 0) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Problem encountered - no customers were updated.'; END IF;
   COMMIT;
   END$$