Mysql – OUT or INOUT parameter not working

argumentsMySQLoutstored-procedures

If I create a stored procedure in MySQL with an OUT or INOUT parameter, it does not work. Even if I leave the stored procedure empty. The DDL is as follows:

CREATE DEFINER=`root`@`localhost` PROCEDURE `testing_inout`(`a` int,INOUT `b` int)
BEGIN

END

The first parameter is a regular IN parameter. The second one is an INOUT paremeter (even if set as OUT the same problem persists)

When I call this stored procedure, I get an error stating:

OUT or INOUT argument 2 for routine db_name.testing_inout is not a
variable or NEW pseudo-variable in BEFORE trigger.

This error persists even if code is written inside the stored procedure. I am running mysql version 5.1.41.

This is a big problem for me because I cannot output the result of a recursive stored procedure because of this error.

Best Answer

it works for me

mysql> CREATE DEFINER=`root`@`localhost` PROCEDURE `testing_inout`(`a` int,INOUT `b` int)
    -> BEGIN
    -> 
    -> END
    -> ;
Query OK, 0 rows affected (0.00 sec)

The second parameter because it's inout it need to be a variable:

mysql> set @c:=1;
Query OK, 0 rows affected (0.00 sec)

mysql> call testing_inout(1,@c);
Query OK, 0 rows affected (0.00 sec)

if you try this it will return 1:

delimiter |
CREATE DEFINER=`root`@`localhost` PROCEDURE `testing_inout`(`a` int,INOUT `b` int)
BEGIN
select b;
END
|
delimiter ;

mysql> call testing_inout(1,@c);
+------+
| b    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
Related Topic