Using OUT parameter to return the status code and message from a procedure : Parameters « Procedure Function « SQL / MySQL

1. SQL / MySQL
2. Procedure Function
3. Parameters

Using OUT parameter to return the status code and message from a procedure

    
mysql>
mysql> delimiter $$
mysql>
mysql> CREATE PROCEDURE myProc
    ->     (in_dob DATE,
    ->      OUT status_code INT,
    ->      OUT status_message VARCHAR(30))
    -> BEGIN
    ->
    ->   IF DATE_SUB(now(), INTERVAL 18 YEAR) <in_dob THEN
    ->     SET status_code=-1;
    ->     SET status_message="Error: employee is less than 18 years old";
    ->   ELSE
    ->     SET status_code=0;
    ->     SET status_message="OK";
    ->   END IF;
    -> END$$
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> delimiter ;
mysql>
mysql> set @myCode = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> set @myMessage='';
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> call myProc('1999-01-01',@myCode,@myMessage);
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> drop procedure myProc;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> select @myCode;
+---------+
| @myCode |
+---------+
| -1      |
+---------+
1 row in set (0.02 sec)

mysql>
mysql> select @myMessage;
+--------------------------------+
| @myMessage                     |
+--------------------------------+
| Error: employee is less than 1 |
+--------------------------------+
1 row in set (0.00 sec)

          
    
    
  

Related examples in the same category