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[()]

A stored procedure without parameters can be called without using parentheses. For example, CALL proc() and CALL proc are equivalent.

CALL can use declared OUT or INOUT parameters to pass values back to the caller, and calling the ROW_COUNT() function can retrieve the number of rows affected after all statements in the routine have executed. For the C API, this can be achieved by calling the mysql_affected_rows() function.

OUT or INOUT parameters can be passed using user variables to retrieve values from the stored procedure, and the variable values can be checked after the procedure returns. If the procedure is called from another stored procedure or function, routine parameters or local routine variables can also be passed as IN or INOUT parameters. For INOUT parameters, the value must be initialized 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 the current value by 2.

obclient> DELIMITER //

obclient> 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:

obclient> DELIMITER ;

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

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

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