CALLsp_name
([parameter
[,...]]) CALLsp_name
[()]
The CALL
statement invokes a stored
procedure that was defined previously with
CREATE PROCEDURE
.
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
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 the C
API, call the
mysql_affected_rows()
function.
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.5.3-m3-log | 11 | +--------------+------------+
In prepared CALL
statements used
with PREPARE
and
EXECUTE
, placeholders can be used
for IN
parameters. For OUT
and INOUT
parameters, placeholder support is
available as of MySQL 5.5.3. 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 | +--------------+------------+ | 5.5.3-m3-log | 11 | +--------------+------------+
Before MySQL 5.5.3, placeholder support is not available for
OUT
or INOUT
parameters. To
work around this limitation for OUT
and
INOUT
parameters, forego the use of
placeholders; instead, refer to user variables in the
CALL
statement itself and do not
specify them in the EXECUTE
statement:
mysql>SET @increment = 10;
mysql>PREPARE s FROM 'CALL p(@version, @increment)';
mysql>EXECUTE s;
mysql>SELECT @version, @increment;
+--------------+------------+ | @version | @increment | +--------------+------------+ | 5.5.0-m2-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
). As of MySQL 5.5.3,
CLIENT_MULTI_RESULTS
is enabled by default.
To process the result of a CALL
statement executed via
mysql_query()
or
mysql_real_query()
, use a loop
that calls mysql_next_result()
to
determine whether there are more results. For an example, see
Section 21.9.12, “C API Support for Multiple Statement Execution”.
For programs written in a language that provides a MySQL
interface, there is no native method prior to MySQL 5.5.3 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. To handle an
INOUT
parameter, execute a statement prior to
the CALL
that sets the
corresponding user variable to the value to be passed to the
procedure.
The following example illustrates the technique (without error
checking) for the stored procedure p
described
earlier that has an OUT
parameter and an
INOUT
parameter:
mysql_query(mysql, "SET @increment = 10"); mysql_query(mysql, "CALL p(@version, @increment)"); mysql_query(mysql, "SELECT @version, @increment"); 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
@version
and @increment
,
respectively.
As of MySQL 5.5.3, C programs can use the prepared-statement
interface to execute CALL
statements and access OUT
and
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 21.9.15, “C API Support for Prepared CALL
Statements”. Languages that
provide a MySQL interface can use prepared
CALL
statements to directly
retrieve OUT
and INOUT
procedure parameters.
User Comments
Here is an example by using you can pass name as parameter and can get customers id
Step 1.
DROP PROCEDURE sp_get_rec;
Step 2.
CREATE PROCEDURE sp_get_rec(OUT str_id int(3) , match_name CHAR(20) )
BEGIN
SELECT id into str_id FROM authors where name=match_name;
END
Step 3.
mysql > call sp_get_rec(@id,'Arvind');
Step 4.
mysql> select @id;
after doing this it will display id associated with that name if its in DB.
Thank you.
Add your own comment.