CALL statement

The CALL statement invokes a stored procedure.

Syntax


>>-CALL--procedure-name--argument-list-------------------------><


argument-list:

>>-+----------------------------------+------------------------><
   |    .-,----------------------.    |   
   |    V                        |    |   
   '-(----+-SQL-variable-name--+-+--)-'   
          +-SQL-parameter-name-+          
          +-expression---------+          
          '-NULL---------------'

Description

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 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 procedures. 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)