MySQL SIGNAL RESIGNAL
This article will cover how to use SIGNAL and RESIGNAL statements for raising error conditions inside stored programs.
MySQL SIGNAL Statement
The SIGNAL query is a mechanism used to return a warning or error message appearing during the execution of a stored program, such as stored procedure, trigger or event, or stored function. This statement provides error information to an error handler, the outer portion of an application, or the client. It also provides control over error characteristics such as error number, SQLSTATE, value, and message in stored procedures. The SIGNAL statement does not require any privileges for their execution.
Syntax
The following is the basic syntax to use the SIGNAL statement:
Here, the SQLSTATE or a condition_name declared by the DECLARE CONDITION statement indicates the error value to be returned. It is to note that the SIGNAL statement must have an SQLSTATE value or a named condition defined with an SQLSTATE value.
The SQLSTATE consists of five alphanumeric characters. We do not use the SQLSTATE code with ’00’ because it indicates success, which is not valid for raising an error. A Bad SQLSTATE error is found when the value is invalid. If we want to catch-all error handling, we must assign the SQLSTATE code ‘45000’, which means an unhandled user-defined exception.
The optional SET clause is used to provide the caller with information. If there is a need for returning multiple condition information item names, it is required to use a comma operator to separate each name/value pair.
The condition_information_item_name can be any of the following and must be specified only once in the SET clause. Otherwise, it will return a duplicate condition information item error.
- CLASS_ORIGIN
- MESSAGE_TEXT
- MYSQL_ERRNO
- CONSTRAINT_NAME
- SCHEMA_NAME
- TABLE_NAME
- CURSOR_NAME, etc.
Let us understand SIGNAL implementation with an example.
Example
Here we will first create a procedure named addStudent. This procedure first counts the total students with the input student id that we pass in the stored procedure. Second, it checks them in the table, and if the number of students is not 1, it will return an error with SQLSTATE 45000 along with the message student id does not exist in the student_info table. We will notice that 45000 is a generic SQLSTATE for an unhandled user-defined exception.
Here is the complete procedure code:
When we call the procedure with the provided student detail, we will get an error message.
Here is the output:
MySQL RESIGNAL Statement
MySQL provides RESIGNAL statement for raising a warning or error condition similar to the SIGNAL statement in terms of functionality and syntax, except that:
- The RESIGNAL statement must be used within an error or warning handler themselves. Otherwise, MySQL generates an error message: RESIGNAL when the handler is not active.
- The RESIGNAL statement can be used without any attributes, even the SQLSTATE value or attributes as in the SIGNAL statement.
If we use only RESIGNAL statement in the stored program, all attributes are the same as those passed to the condition handler.
Example
Let us understand it with an example where the procedure changes the error message before issuing it to the caller.
If we call the procedure using the below statement, we will get an error message:
Here is the result:
