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:

  SIGNAL SQLSTATE | condition_name;  

SET condition_information_item_name1 = value1,  

    condition_information_item_name1 = value2, etc;  

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:

CREATE PROCEDURE addStudent(  

IN stud_id INT,   

IN stud_name VARCHAR(35),   

IN subject VARCHAR(25),   

IN marks INT,   

IN phone VARCHAR(15)  

)  

BEGIN  

    DECLARE C INT;  

    SELECT COUNT(student_id) INTO C  

    FROM student_info  

    WHERE  student_id = stud_id;  

  

    -- check if student id not exists  

    IF(C != 1) THEN   

        SIGNAL SQLSTATE '45000'  

        SET MESSAGE_TEXT = 'Student id not found in student_info table';  

    END IF;  

END $$  

DELIMITER ;  

When we call the procedure with the provided student detail, we will get an error message.

CALL addStudent (16, 'Kevin', 'science', 66, '69934569359');  

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.

   DELIMITER $$  

CREATE PROCEDURE getDevision (IN numerator INT, IN denominator INT, OUT res double)  

BEGIN  

    DECLARE Division_By_Zero CONDITION FOR SQLSTATE '45000';  

    DECLARE CONTINUE HANDLER FOR Division_By_Zero   

    RESIGNAL SET MESSAGE_TEXT = 'The denominator cannot be zero';  

    --   

    IF denominator = 0 THEN  

        SIGNAL Division_By_Zero;  

    ELSE  

        SET res := numerator / denominator;  

    END IF;  

END $$  

DELIMITER ;   

If we call the procedure using the below statement, we will get an error message:

CALL getDivision (25, 0, @res);  

Here is the result: