When working with MySQL stored procedures, managing exceptions is important to prevent abrupt termination of procedures and to provide meaningful error information. This is achieved using the MySQL SIGNAL statement and the DECLARE … HANDLER statement.

The MySQL SIGNAL Statement

The MySQL SIGNAL statement is used to convey error information in stored procedures. It ensures that exceptions are properly handled, preventing sudden procedure termination.

Exception Handling with DECLARE … HANDLER

You can use the DECLARE … HANDLER statement to effectively manage exceptions in MySQL. It allows you to specify how different types of exceptions should be handled within a stored procedure. By using this statement in conjunction with SIGNAL, you can enable precise control over error handling.

Customizing Error Messages

The SIGNAL statement allows for the customization of error messages using the SET MESSAGE_TEXT command. This is helpful for modifying error messages to provide more meaningful feedback to handlers, applications, or clients.

Syntax

Following is the syntax of the MySQL SIGNAL Statement −

SIGNAL condition_value [SET signal_information_item]

Where,

• condition_value represents the error value to be returned, which can be either a “sqlstate_value” or a “condition_name”.

• signal_information_item allows you to set additional information related to the error condition. You can specify various signal information items like CLASS_ORIGIN, SUBCLASS_ORIGIN, MESSAGE_TEXT, MYSQL_ERRNO, CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, CATALOG_NAME, SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, or CURSOR_NAME.

Example

In this example, we create a procedure that accepts the short form of degrees and returns their full forms. If we provide an invalid degree i.e. value other than B-Tech, M-Tech, BSC and MSC, an error message is generated using the SIGNAL statement −

DELIMITER //
CREATE PROCEDURE example(IN degree VARCHAR(20), OUT full_form Varchar(50))
BEGIN
IF degree=''B-Tech'' THEN SET full_form = ''Bachelor of Technology''; 
ELSEIF degree=''M-Tech'' THEN SET full_form = ''Master of Technology''; 
ELSEIF degree=''BSC'' THEN SET full_form = ''Bachelor of Science'';
ELSEIF degree=''MSC'' THEN SET full_form = ''Master of Science'';
ELSE
SIGNAL SQLSTATE ''01000''
SET MESSAGE_TEXT = ''Choose from the existing values'', MYSQL_ERRNO = 12121;
SIGNAL SQLSTATE ''45000''
SET MESSAGE_TEXT = ''Given degree is not valid'', MYSQL_ERRNO = 1001;
END IF;
END //
DELIMITER ;

You can call the above procedure to retrieve the result as shown below −

CALL example(''BSC'', @fullform);

You can retrieve the value of the variable using the following SELECT statement −

SELECT @fullform;

Following is the output obtained −

@fullform
Bachelor of Science

If you pass an invalid value to the procedure, it will generate an error message as shown below −

CALL example (''BBC'', @fullform);

The output obtained is as follows −

ERROR 1001 (45000): Given degree is not valid

Example

Following is another example demonstrating exception handling with the SIGNAL statement. Here, we declare a condition and use SIGNAL to trigger exceptions based on certain conditions −

DELIMITER //
CREATE PROCEDURE example (num INT)
BEGIN
DECLARE testCondition CONDITION FOR SQLSTATE ''45000'';
IF num < 0 THEN
SIGNAL SQLSTATE ''01000'';
ELSEIF num > 150 THEN
SIGNAL SQLSTATE ''45000'';
END IF;
END //
DELIMITER ;

You can call the above procedure by passing two values as shown below −

CALL example(15);

Following is the output obtained −

Query OK, 0 rows affected (0.00 sec)

Calling the procedure by passing the second value −

CALL example(160);

The result produced is as shown below −

ERROR 1644 (45000): Unhandled user-defined exception condition

Example

You can customize error messages using SET MESSAGE_TEXT with the SIGNAL statement as shown in this modified example −

DELIMITER //
CREATE PROCEDURE example (num INT)
BEGIN
DECLARE testCondition CONDITION FOR SQLSTATE ''45000'';
IF num < 0 THEN
SIGNAL SQLSTATE ''01000'';
ELSEIF num > 150 THEN
SIGNAL SQLSTATE ''45000'' SET MESSAGE_TEXT = ''Number higher than the limit set'';
END IF;
END //
DELIMITER ;

We get the following output −

Query OK, 0 rows affected (0.01 sec)

You can call the above procedure by passing two values as shown below −

CALL example(20);

Following is the output obtained −

Query OK, 0 rows affected (0.00 sec)

Calling the procedure by passing the second value −

CALL example(160);

You can observe in the output below, the error message displayed is customized according to the user −

ERROR 1644 (45000): Number higher than the limit set