Pass variable to a procedure as the OUT parameter : Parameters « Procedure Function « SQL / MySQL

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

Pass variable to a procedure as the OUT parameter

    
mysql>
mysql> delimiter $$
mysql>
mysql> CREATE PROCEDURE myProc
    ->     (in_due_date DATE,
    ->      OUT status_code INT,
    ->      OUT status_message VARCHAR(30))
    -> BEGIN
    ->   DECLARE days_past_due INT;
    ->
    ->   SET days_past_due=FLOOR(DATEDIFF(now(),in_due_date));
    ->   IF days_past_due>90 THEN
    ->     SET status_code=-2;
    ->     SET status_message='more than 90 days overdue';
    ->   ELSEIF days_past_due >30 THEN
    ->     SET status_code=-1;
    ->     SET status_message='more than 30 days overdue';
    ->   ELSE
    ->     SET status_code=0;
    ->     SET status_message='OK';
    ->
    ->   END IF;
    -> END$$
Query OK, 0 rows affected (0.00 sec)

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

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

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

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

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

mysql>
mysql> select @myMessage;
+---------------------------+
| @myMessage                |
+---------------------------+
| more than 90 days overdue |
+---------------------------+
1 row in set (0.00 sec)

mysql>

          
    
    
  

Related examples in the same category