Raising Error Conditions with MySQL SIGNAL and RESIGNAL

In this blog, you will study how to practice SIGNAL and RESIGNAL statements to raise error situations inside stored procedures.

MySQL SIGNAL statement

You practice the SIGNAL statement to return an error or warning complaint to the caller from a stored program e.g., stored procedure, stored function, trigger or event. The SIGNAL statement delivers you with switch over which info for returning such as value and message SQLSTATE.

The subsequent demonstrates syntax of the SIGNAL statement:

Ensuing the SIGNAL keyword is a SQLSTATE value or a state name declared by the DECLARE CONDITION statement. Notice that the SIGNAL statement essential always agree a SQLSTATE value or a named state that well-defined with an SQLSTATE value.

To make available the caller with info, you use the SET section. If you want to return multiple condition data item names with values, you need to distinct each name/value pair by a comma.

The condition_information_item_name can be MESSAGE_TEXT, MYSQL_ERRORNO, CURSOR_NAME, etc.

The subsequent stored procedure adds an order line item into a current sales order. It matters an error message if the order number does not occur.

Primary, it counts the instructions with the input order number that we pass to the stored procedure.

Another, if the number of instructions is not 1, it raises an error with SQLSTATE 45000 laterally with an error message saying that order number does not occur in the orders table.

Notice that 45000 is a general SQLSTATE value that demonstrates an unhandled user-defined exception.

If we call the stored procedure AddOrderItem() and pass a missing order number, we will get an error message.

MySQL RESIGNAL statement

In addition the SIGNAL statement, MySQL also delivers the RESIGNAL statement used to raise a warning or error state.

The RESIGNAL statement is alike to SIGNAL statement in term of functionality and syntax, excluding that:

• Your essential use the RESIGNAL statement within an error or warning handler, or else, you will get an error message saying that “RESIGNAL when handler is not active”. Note that you can use SIGNAL statement wherever inside a stored procedure.

• You can neglect all attributes of the RESIGNAL statement, even the SQLSTATE value.

If you use the RESIGNAL statement on your own, all attributes are the equal as the ones agreed to the condition handler.

The subsequent stored procedure changes the error message formerly delivering it to the caller.

Let’s call the Divide() stored procedure.

In this blog, we have revealed you how to raise error situations inside stored programs using SIGNAL and RESIGNAL statements.