Mega Code Archive
Using OUT parameter to return the status code and message from a procedure
<![CDATA[
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)
]]>