CALL statement
The CALL statement invokes a stored procedure.
Syntax
>>-+--------+--CALL--procedure-name--argument-list------------->< '-label:-'
argument-list: >>-+----------------------------------+------------------------>< | .-,----------------------. | | V | | '-(----+-SQL-variable-name--+-+--)-' +-SQL-parameter-name-+ +-expression---------+ '-NULL---------------'
Description
- label
- Specifies the label for the CALL statement. The label name cannot be the same as the name of the SQL routine or another label within the same scope. For additional information, see References to labels.
- procedure-name
- Identifies the stored procedure to call. The procedure name must identify a stored procedure that exists at the current server.
- argument-list
- Identifies a list of values to be passed as parameters
to the stored procedure. The nth value corresponds
to the nth parameter in the procedure. The number
of parameters must be the same as the number of parameters defined
for the stored procedure. See CALL for more information.
Control is passed to the stored procedure according to the calling conventions for SQL routines. When execution of the stored procedure is complete, the value of each parameter of the stored procedure is assigned to the corresponding parameter of the CALL statement defined as OUT or INOUT.
- SQL-variable-name
- Specifies an SQL variable as an argument to the stored procedure. For an explanation of references to SQL variables, see References to SQL parameters and SQL variables.
- SQL-parameter-name
- Specifies an SQL parameter as an argument to the stored procedure. For an explanation of references to SQL parameters, see References to SQL parameters and SQL variables.
- expression
- The parameter is the result of the specified expression,
which is evaluated before the stored procedure is invoked. If expression is
a single SQL-parameter-name or SQL-variable-name,
the corresponding parameter of the procedure can be defined as IN,
INOUT, or OUT. Otherwise, the corresponding parameter of the procedure
must be defined as IN. If the result of the expression can
be the null value, either the description of the procedure must allow
for null parameters or the corresponding parameter of the stored procedure
must be defined as OUT.
The following additional rules apply depending on how the corresponding parameter was defined in the CREATE PROCEDURE statement for the procedure:
- IN expression can contain references to multiple SQL parameters or variables. In addition to the rules stated in Expressions for expression, expression cannot include a column name, an aggregate function, or a user-defined function that is sourced on an aggregate function.
- INOUT or OUT expression can only be a single SQL parameter or variable.
- NULL
- The parameter is a null value. The corresponding parameter of the procedure must be defined as IN and the description of the procedure must allow for null parameters.
Notes
See CALL for more information on the SQL CALL statement.
Examples
Call stored procedure proc1 and
pass SQL variables as parameters.
CALL proc1(v_empno, v_salary)