13.2.1 CALL Statement
CALL sp_name([parameter[,...]]) CALL sp_name[()]
Stored procedures that take no arguments can be invoked without parentheses. That is,
CALL p() and
CALL p are equivalent.
CALL can pass back values to its caller using parameters that are declared as
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 the
ROW_COUNT() function; from the C API, call the
For information about the effect of unhandled conditions on procedure parameters, see Section 18.104.22.168, “Condition Handling and OUT or INOUT Parameters”.
To get back a value from a procedure using an
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
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 an
INOUT value that the procedure increments by one from its current value:
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;
Before calling the procedure, initialize the variable to be passed as the
INOUT parameter. After calling the procedure, the values of the two variables will have been set or modified:
mysql> SET @increment = 10; mysql> CALL p(@version, @increment); mysql> SELECT @version, @increment; +------------------+------------+ | @version | @increment | +------------------+------------+ | 5.7.20-debug-log | 11 | +------------------+------------+
mysql> SET @increment = 10; mysql> PREPARE s FROM 'CALL p(?, ?)'; mysql> EXECUTE s USING @version, @increment; mysql> SELECT @version, @increment; +------------------+------------+ | @version | @increment | +------------------+------------+ | 5.7.20-debug-log | 11 | +------------------+------------+
To write C programs that use the
CALL SQL statement to execute stored procedures that produce result sets, the
CLIENT_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 will produce result sets, so it is necessary to assume that they will.
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 passing
CLIENT_MULTI_STATEMENTS (which also enables
CLIENT_MULTI_RESULTS is enabled by default.
To process the result of a
CALL statement executed using
mysql_real_query(), use a loop that calls
mysql_next_result() to determine whether there are more results. For an example, see Section 27.7.15, “C API Multiple Statement Execution Support”.
C programs can use the prepared-statement interface to execute
CALL statements and access
INOUT parameters. This is done by processing the result of a
CALL statement using a loop that calls
mysql_stmt_next_result() to determine whether there are more results. For an example, see Section 27.7.17, “C API Prepared CALL Statement Support”. Languages that provide a MySQL interface can use prepared
CALL statements to directly retrieve
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, see Section 8.10.4, “Caching of Prepared Statements and Stored Programs”.