How to exit from a MySQL/ MariaDB Stored Procedure/ Function Prematurely

How to exit from a MySQL/ MariaDB Stored Procedure/ Function Prematurely

Category : How-to

Get Social!

MySQL and MariaDB enable you to define your own error conditions and to report back to the SQL client both a return code and an error message. As soon as you raise the condition then MySQL/ MariaDB will halt any further execution of the code and report the error back to the client. This can help the user calling the function understand what went wrong, rather than seeing a generic database error message.

DECLARE error_flag INT DEFAULT 0;
DECLARE REF_MISSING CONDITION FOR SQLSTATE '45000';

-- Your code, set the error_flag in the event of an error

IF (error_flag) THEN
    SIGNAL REF_MISSING
    SET MESSAGE_TEXT = 'An error occurred!';
END IF;

The above code defines a custom condition with an error code of 45000 which is the suggested user defined error code. Other error codes are available, which you may have seen, but are reserved by the database server to use for specific database error events – it’s best not to mix your user defined messages with these. The function then checks if the error_flag has been set and, if it has, halts further code execution and returns the error “An error occurred!” to the client.

View from MySQL Workbench

You could simplify this by just calling the below code at the point the error is detected, if you are already catching an error event in your stored procedure or function by simply using the below code without the error_flag declaration.

SIGNAL REF_MISSING
SET MESSAGE_TEXT = 'An error occurred!';


Leave a Reply

Visit our advertisers

Quick Poll

Do you use GlusterFS in your workplace?

Visit our advertisers