Error handling pre-5.7

from the Artful Common Queries page

Until you upgrade to 8.0, assuming a target table t( somecol t int ), an errorlog table ( ts timestamp, msg text ), and a desire for handling particular database errors, here's an example of error handling in stored procedure transaction code ... 
delimiter go
CREATE PROCEDURE errdemo( pvalue INT, OUT pResult INT )
BEGIN
  DECLARE bad_key CONDITION FOR SQLSTATE '23000';
  DECLARE EXIT HANDLER FOR bad_key
    BEGIN
      INSERT INTO errlog( dt, msg )
        VALUES( Now(), Concat('Errdemo:Insert on t failed; invalid key.') );
      SET presult=-1;
      ROLLBACK;
    END;
  START TRANSACTION;
    INSERT INTO t VALUES( pvalue, NULL ) ;
    SET pResult=Row_Count();
  COMMIT;
END;
go
delimiter ;

Last updated 1 Dec 2024