Using SIGNAL in MySQL 5.5

from the Artful MySQL Tips List


Here is an example from Chapter 8 of Get It Done with MySQL 5&Up:

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 |
+-------+------+------------------+

And here is a RESIGNAL example from the same chapter:

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 ;


Return to the Artful MySQL Tips page