CALL

The CALL statement is used to invoke a stored procedure that was previously defined using CREATE PROCEDURE.

The syntax for the CALL statement is as follows:

CALL sp_name([parameter[,...]])
CALL sp_name[()]

When calling a stored procedure without parameters, the parentheses can be omitted. For example, CALL proc() and CALL proc are equivalent.

The CALL statement can pass values back to the caller using declared OUT or INOUT parameters. Additionally, calling the ROW_COUNT() function allows you to retrieve the number of rows affected after all statements in the routine have been executed. For the C API, you can also use the mysql_affected_rows() function to achieve this.

To retrieve values from a stored procedure using OUT or INOUT parameters, you can pass them through user variables and check the variable values after the procedure returns. If the procedure is called from another stored procedure or function, you can also pass routine parameters or local variables as IN or INOUT parameters. For INOUT parameters, initialize the value before passing it to the procedure.

In the following example, the stored procedure has an OUT parameter set to the current server version and an INOUT parameter that increments by 2 from its current value.

DELIMITER //

CREATE PROCEDURE proc (OUT ver_param VARCHAR(100), INOUT incr_param INT)
   BEGIN
     # Set the value of the OUT parameter
     SELECT VERSION() INTO ver_param;
     # Increment the value of the INOUT parameter
     SET incr_param = incr_param + 2;
   END //
Query OK, 0 rows affected

Before calling the procedure, initialize the variable that will be passed as an INOUT parameter. After calling the procedure, you can see that both variables have been set or modified, as shown below:

DELIMITER ;

SET @increment = 1;
Query OK, 0 rows affected

CALL proc(@obversion, @increment);
+------------------------------+------------+
| ver_param                    | incr_param |
+------------------------------+------------+
| 5.7.25-OceanBase_CE-v4.0.0.0 |          3 |
+------------------------------+------------+
1 row in set

SELECT @obversion, @increment;
+------------------------------+------------+
| @obversion                   | @increment |
+------------------------------+------------+
| 5.7.25-OceanBase_CE-v4.0.0.0 |          3 |
+------------------------------+------------+
1 row in set