MySQL :: MySQL 8.0 Reference Manual :: 15.2.1 CALL Statement (original) (raw)

15.2.1 CALL Statement

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

The CALL statement invokes a stored procedure that was defined previously withCREATE PROCEDURE.

Stored procedures that take no arguments can be invoked without parentheses. That is, CALL p() andCALL p are equivalent.

CALL can pass back values to its caller using parameters that are declared asOUT or INOUT parameters. When the procedure returns, a client program can also obtain the number of rows affected for the final statement executed within the routine: At the SQL level, call theROW_COUNT() function; from the C API, call themysql_affected_rows() function.

For information about the effect of unhandled conditions on procedure parameters, seeSection 15.6.7.8, “Condition Handling and OUT or INOUT Parameters”.

To get back a value from a procedure using anOUT or INOUT parameter, pass the parameter by means of a user variable, and then check the value of the variable after the procedure returns. (If you are calling the procedure from within another stored procedure or function, you can also pass a routine parameter or local routine variable as an IN or INOUT parameter.) For an INOUT parameter, initialize its value before passing it to the procedure. The following procedure has an OUT parameter that the procedure sets to the current server version, and anINOUT value that the procedure increments by one from its current value:

DELIMITER //

CREATE PROCEDURE p (OUT ver_param VARCHAR(25), INOUT incr_param INT)
BEGIN
  # Set value of OUT parameter
  SELECT VERSION() INTO ver_param;
  # Increment value of INOUT parameter
  SET incr_param = incr_param + 1;
END //

DELIMITER ;

Before calling the procedure, initialize the variable to be passed as the INOUT parameter. After calling the procedure, you can see that the values of the two variables are set or modified:

mysql> SET @increment = 10;
mysql> CALL p(@version, @increment);
mysql> SELECT @version, @increment;
+----------+------------+
| @version | @increment |
+----------+------------+
| 8.0.42   |         11 |
+----------+------------+

In prepared CALL statements used with PREPARE andEXECUTE, placeholders can be used for IN parameters, OUT, andINOUT parameters. These types of parameters can be used as follows:

mysql> SET @increment = 10;
mysql> PREPARE s FROM 'CALL p(?, ?)';
mysql> EXECUTE s USING @version, @increment;
mysql> SELECT @version, @increment;
+----------+------------+
| @version | @increment |
+----------+------------+
| 8.0.42   |         11 |
+----------+------------+

To write C programs that use theCALL SQL statement to execute stored procedures that produce result sets, theCLIENT_MULTI_RESULTS flag must be enabled. This is because each CALL returns a result to indicate the call status, in addition to any result sets that might be returned by statements executed within the procedure. CLIENT_MULTI_RESULTS must also be enabled if CALL is used to execute any stored procedure that contains prepared statements. It cannot be determined when such a procedure is loaded whether those statements produce result sets, so it is necessary to assume that they do so.

CLIENT_MULTI_RESULTS can be enabled when you call mysql_real_connect(), either explicitly by passing the CLIENT_MULTI_RESULTS flag itself, or implicitly by passingCLIENT_MULTI_STATEMENTS (which also enablesCLIENT_MULTI_RESULTS).CLIENT_MULTI_RESULTS is enabled by default.

To process the result of a CALL statement executed usingmysql_query() ormysql_real_query(), use a loop that calls mysql_next_result() to determine whether there are more results. For an example, seeMultiple Statement Execution Support.

C programs can use the prepared-statement interface to executeCALL statements and accessOUT and INOUT parameters. This is done by processing the result of aCALL statement using a loop that calls mysql_stmt_next_result() to determine whether there are more results. For an example, seePrepared CALL Statement Support. Languages that provide a MySQL interface can use preparedCALL statements to directly retrieve OUT and INOUT procedure parameters.

Metadata changes to objects referred to by stored programs are detected and cause automatic reparsing of the affected statements when the program is next executed. For more information, seeSection 10.10.3, “Caching of Prepared Statements and Stored Programs”.