Errores en procedimientos

Manejo de errores en procedimientos de MySQL

Manejo de excepciones (handlers)

Server Error Message Reference

MySQL Stored Procedures Tutorial

Handling Errors

Raising Errors

GET DIAGNOSTICS Statement

Ejemplo: insertar un empleado en la base de datos proyectosDB

DELIMITER $$
CREATE PROCEDURE do_insert(value VARCHAR(3), name VARCHAR(30))
BEGIN
  -- Declare variables to hold diagnostics area information
  DECLARE code CHAR(5) DEFAULT '00000';
  DECLARE msg TEXT;
  DECLARE nrows INT;
  DECLARE result TEXT;
  DECLARE errorNumber VARCHAR(10);

  -- Declare exception handler for failed insert
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
      GET DIAGNOSTICS CONDITION 1
        code = RETURNED_SQLSTATE, msg = MESSAGE_TEXT, errorNumber = MYSQL_ERRNO;
      SET result = CONCAT('insert failed, error = ', code,', message = ', msg, ', number = ', errorNumber);
    END;

  -- Perform the insert in proyectosDB database
  INSERT INTO empleado (cdemp, nombre) VALUES(value, name);

  -- Check whether the insert was successful
  IF code = '00000' THEN
    GET DIAGNOSTICS nrows = ROW_COUNT;
    SET result = CONCAT('insert succeeded, row count = ', nrows);   
  END IF;

  -- Say what happened
  SELECT result;
END$$
DELIMITER ;

Ejecución del procedimiento do_insert:

SET @p1 = 'B10', @p2 = 'Ana Martín';
CALL do_insert(@p1, @p2);