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>