For information about permissible condition_information_item_name  values, see Signal Condition Information Items. 

The following procedure signals an error or warning depending on the value of pval , its input parameter: 

CREATE PROCEDURE p (pval INT) BEGIN DECLARE specialty CONDITION FOR SQLSTATE ’45000’; IF pval = 0 THEN SIGNAL SQLSTATE ’01000’; ELSEIF pval = 1 THEN SIGNAL SQLSTATE ’45000’ SET MESSAGE_TEXT = ’An error occurred’; ELSEIF pval = 2 THEN SIGNAL specialty SET MESSAGE_TEXT = ’An error occurred’; ELSE SIGNAL SQLSTATE ’01000’ SET MESSAGE_TEXT = ’A warning occurred’, MYSQL_ERRNO = 1000; SIGNAL SQLSTATE ’45000’ SET MESSAGE_TEXT = ’An error occurred’, MYSQL_ERRNO = 1001; END IF; END; 

If pval  is 0, p()  signals a warning because SQLSTATE  values that begin with ’01’  are signals in the warning class. The warning does not terminate the procedure, and can be seen with SHOW WARNINGS after the procedure returns. 

If pval  is 1, p()  signals an error and sets the MESSAGE_TEXT  condition information item. The error terminates the procedure, and the text is returned with the error information. 

If pval  is 2, the same error is signaled, although the SQLSTATE  value is specified using a named condition in this case. 

If pval  is anything else, p()  first signals a warning and sets the message text and error number condition information items. This warning does not terminate the procedure, so execution continues and p()  then signals an error. The error does terminate the procedure. The message text and error number set by the warning are replaced by the values set by the error, which are returned with the error information. 

SIGNAL  is typically used within stored programs, but it is a MySQL extension that it is permitted outside handler context. For example, if you invoke the mysql  client program, you can enter any of these statements at the prompt: 

SIGNAL SQLSTATE ’77777’; CREATE TRIGGER t_bi BEFORE INSERT ON t FOR EACH ROW SIGNAL SQLSTATE ’77777’; CREATE EVENT e ON SCHEDULE EVERY 1 SECOND DO SIGNAL SQLSTATE ’77777’; 

SIGNAL  executes according to the following rules: 

If the SIGNAL  statement indicates a particular SQLSTATE  value, that value is used to signal the condition specified. Example: 

CREATE PROCEDURE p (divisor INT) BEGIN IF divisor = 0 THEN SIGNAL SQLSTATE ’22012’; END IF; END; 

