The SIGNAL statements are used as a way to return an error message appearing during code execution. These statements provide error information to an error handler, outer portion of the application or to the client. It returns information of error number, SQLSTATE, value and message that occurs in stored procedures. The syntax of the SIGNAL statement is:

Syntax

SIGNAL SQLSTATE | condition_name;

Where condition_name is the set using SET statements. The multiple signal items declared as comma separated, name/value pairs are optional:

SET condition_1 = value_1,

   condition _2 = value_2, etc;

The 'condition_1', 'condition_2' ... etc, can be any if these: CLASS_ORIGIN, MESSAGE_TEXT, MYSQL_ERRNO, TABLE_NAME, COLUMN_NAME, ... The SQLSTATE or condition_name in the SIGNAL indicates the error value that is to be returned. To signal a generic SQLSTATE value, use '45000', which means "unhandled user-defined exception."

The example below shows the SIGNAL implementation. The procedure takes the temperature as input, it checks in the database for the status of the machines working in that temperature. If the status returned is 0, it indicates that the machine is not working and the message is sent to the outer function using SIGNAL statements. If the machine status if OK, the details of the machine is displayed.

DELIMITER $$ 

CREATE PROCEDURE settemp(IN temp INT)

BEGIN

   DECLARE T INT; 

   SELECT status INTO T

   FROM tblmachine 

   WHERE temperature = temp;

   IF(T = 0) THEN 

      SIGNAL SQLSTATE '45000'

      SET MESSAGE_TEXT = 'The machine not working';

   ELSE

      SELECT * FROM tblmachine WHERE temperature = temp;

   END IF;

END $$

DELIMITER ;

CALL settemp(35);

The output from above given example is: '#1644 - The machine not working'.

Resignal

The RESIGNAL statement is the same as the SIGNAL one except that the RESIGNAL statement should be used within the error or the warning handler themselves. If the RESIGNAL statement is used outside, the MySQL generates an error saying RESIGNAL when handler is not active. The RESIGNAL statement can be used without any attributes or with attributes as in SIGNAL statement. The above example can be used with RESIGNAL instead, as seen below:

DELIMITER $$

CREATE PROCEDURE settemp(IN temp INT)

BEGIN 

   DECLARE tempexceed CONDITION FOR SQLSTATE '45000';

   DECLARE T INT; 

   DECLARE CONTINUE HANDLER FOR tempexceed

   BEGIN

      RESIGNAL SET MESSAGE_TEXT = 'The machine not working';

   END;

 

   SELECT status INTO T

   FROM tblmachine

   WHERE temperature = temp; 

   IF(T < 35) THEN

      SIGNAL tempexceed;

   ELSE

      SELECT * FROM tblmachine WHERE temperature = temp;

   END IF;  

END; $$

DELIMITER ;

CALL settemp(12);

The output from above given example is: '#1644 - The machine not working'.