MySQL - Condition Handling and OUT or INOUT Parameters

If an exception occurs in a procedure, and if it has OUT or INOUT parameters, the procedure terminates abruptly without propagating value for the OUT parameter to the caller.

If you handle the exceptions using CONTINUE or EXIT handler that contains a RESIGNAL statement, the RESIGNAL statement retrieves the information in the stack of the diagnostic area. If the exception occurred is an error, then the values of the OUT or INOUT parameters are not propagated back.

Example

Following procedure accepts the short form of the degrees and returns the full forms of them. If we pass a value other than B-Tech, M-Tech, BSC, MSC it generates an error message.

If you observe incase of an exception the output value is not propagated back to the caller.

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
         RESIGNAL SQLSTATE '01000'
      SET MESSAGE_TEXT = 'Choose from the existing values', 
	  MYSQL_ERRNO = 12121;
         RESIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Given degree is not valid', MYSQL_ERRNO = 1001;
      END IF;
   END //
DELIMITER ;

You can call the above procedure as shown below −

CALL example('BSC', @fullform);

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

SELECT @fullform;

Output

Following is the output of the above query −

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

CALL procedureEx ('BBC', @fullform);
ERROR 1001 (45000): Given degree is not valid

Example

Following is another example −

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

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

DELIMITER ;
CALL example(15);
CALL example(160);
ERROR 1644 (45000): Unhandled user-defined exception condition