DROP PROCEDURE IF EXISTS signaldemo;
DELIMITER go
CREATE PROCEDURE signaldemo( pSignal BOOL )
BEGIN
  BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
      BEGIN
        IF pSignal THEN
          SIGNAL SQLSTATE VALUE '99999' SET MESSAGE_TEXT='Null primary key';
        ELSE
          SELECT 'Null primary key' AS Error;  
        END IF;
      END;
    SELECT 'Running' AS 'Block 1';
    INSERT INTO t VALUES(NULL,NULL);
  END;
  SELECT 'Running' AS 'Block 2';
END;
go
DELIMITER ;
CALL signaldemo(1);
+---------+
| Block 1 |
+---------+
| Running |
+---------+
show errors;
+-------+------+------------------+
| Level | Code | Message          |
+-------+------+------------------+
| Error | 1644 | Null primary key |
+-------+------+------------------+

DROP PROCEDURE IF EXISTS resignaldemo;
DELIMITER go
CREATE PROCEDURE resignaldemo(pResignal BOOL)
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
      SELECT 'Error handler' AS 'WhoAmI';
      IF pResignal THEN RESIGNAL; END IF;
    END;
  INSERT INTO t VALUES(NULL,NULL);
END;
go
DELIMITER ;

Last updated 24 Dec 2024