CALLsp_name
([parameter
[,...]]) CALLsp_name
[()]
The CALL
statement invokes a stored
procedure that was defined previously with
CREATE PROCEDURE
.
As of MySQL 5.1.13, 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.1.32-log | 11 | +------------+------------+
In prepared CALL
statements used
with PREPARE
and
EXECUTE
, placeholder support is
available in MySQL 5.1 for IN
parameters, but not 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.1.32-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
).
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 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.
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.