exception handler

Here is a procedure that will create a new product code or - if the product code already exits, update it with a new name.
The procedure detects an attempt to insert a duplicate value by using an exception handler. If the attempt to insert fails, the error is trapped and an UPDATE is issued in place of the INSERT. Without the exception handler, the stored program execution is stopped, and the exception is passed back unhandled to the calling program.

CREATE PROCEDURE sp_product_code
(in_product_code VARCHAR(2),
in_product_name VARCHAR(30))

BEGIN

DECLARE 1_dupkey_indicator INT DEFAULT 0;
DECLARE duplicate_key CONDITION FOR 1062;
DECLARE CONTINUE HANDLER FOR duplicate_key SET 1_dupkey_indicator =1;

INSERT INTO product_code (product_code, product_name) VALUES (in_product_code, in_product_name);

IF 1_dupkey_indicator THEN
UPDATE product_codes SET product_name=in_product_name WHERE product_code=in_product_code;
END IF;
END


Define a named condition, duplicate_key, that is associated with MySQL error 1062. While this step is not strictly necessary, we recommend that
you define such conditions to improve the readability of your code (you can now reference the error by name instead of number).

Define an error that will trap the duplicate key error and then set the value of the variable 1_dupkey_indicator to 1 (true) if a duplicate key violation is encountered anywhere in the subsequent code.

You can now check the value of 1_dupkey_indicator variable. If it is still 0, then the INSERT was successful and we are done. If the value has been changed to 1 (true), we know that there has been a duplicate key violation. We then run the update statement.