MySQL 8.0 procedure out param is null

Admin 1
Viewed 21

Synopsis

I'm writing a MySQL stored procedure that contains two IN parameters and a single OUT parameter. The value of the OUT parameter appears to be null.

What am I doing wrong here?

Reproduction Steps


DROP PROCEDURE IF EXISTS cbtgeo.add123;

DELIMITER //
CREATE PROCEDURE cbtgeo.add123 (IN val1 INT, IN val2 INT, OUT val3 INT) 
    BEGIN
        SET val3 = @val1 + @val2;
    END//
    
DELIMITER ;

SET @myvar222 = NULL;
CALL cbtgeo.add123(5,6, @myvar222);
SELECT @myvar222;

Expected Result

+-----------+
| @myvar222 |
+-----------+
|        11 |
+-----------+
1 row in set (0.00 sec)

Actual Result

+-----------+
| @myvar222 |
+-----------+
|      NULL |
+-----------+
1 row in set (0.00 sec)

What I've Tried

  • Initializing @myvar222 to 0 instead of NULL
  • Using SET @val3 ... instead of SET val3 ...
mysql
1 Answers

I finally just figured it out.

When you're referencing IN parameters in the body of the stored procedure, you don't use the @ sign.

DROP PROCEDURE IF EXISTS cbtgeo.add123;

DELIMITER //
CREATE PROCEDURE cbtgeo.add123 (IN val1 INT, IN val2 INT, OUT val3 INT) 
    BEGIN
        SET val3 = val1 + val2;
    END//
    
DELIMITER ;

SET @myvar222 = NULL;
CALL cbtgeo.add123(5,6, @myvar222);
SELECT @myvar222;

Result

+-----------+
| @myvar222 |
+-----------+
|        11 |
+-----------+
1 row in set (0.00 sec)
Admin1
Related