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

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

CALL can pass back values to its caller using parameters that are declared as OUT 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 the ROW_COUNT() function; from C, call the mysql_affected_rows() C API function.

As of MySQL 5.0.30, stored procedures that take no arguments can be invoked without parentheses. That is, CALL p() and CALL p are equivalent.

To get back a value from a procedure using an OUT 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 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.0.25-log | 11         | 
+------------+------------+

If you write C programs that use the CALL SQL statement to execute stored procedures that produce result sets, you must set the CLIENT_MULTI_RESULTS flag, either explicitly, or implicitly by setting CLIENT_MULTI_STATEMENTS when you call mysql_real_connect(). This is because each such stored procedure produces multiple results: the result sets returned by statements executed within the procedure, as well as a result to indicate the call status. To process the result of a CALL statement, use a loop that calls mysql_next_result() to determine whether there are more results. For an example, see Section 19.7.9, “C API Handling of Multiple Statement Execution”. CLIENT_MULTI_RESULTS must also be set 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.

For programs written in a language that provides a MySQL interface, there is no native method for directly retrieving the results of OUT or INOUT parameters from CALL statements. To get the parameter values, pass user-defined variables to the procedure in the CALL statement and then execute a SELECT statement to produce a result set containing the variable values. The following example illustrates the technique (without error checking) for a stored procedure p1 that has two OUT parameters.

mysql_query(mysql, "CALL p1(@param1, @param2)");
mysql_query(mysql, "SELECT @param1, @param2");
result = mysql_store_result(mysql);
row = mysql_fetch_row(result);
mysql_free_result(result);

After the preceding code executes, row[0] and row[1] contain the values of @param1 and @param2, respectively.

To handle INOUT parameters, execute a statement prior to the CALL that sets the user variables to the values to be passed to the procedure.