CALL statement
The CALL statement invokes a procedure. The syntax of CALL in an SQL function, SQL procedure, or SQL trigger is a subset of what is supported as a CALL statement in other contexts.
See CALL for details.
Syntax
Description
- label
- Specifies the label for the CALL statement. The label name cannot be the same as the routine name or another label within the same scope. For more information, see References to SQL labels.
- procedure-name
- Identifies the procedure to call. The procedure-name must identify a procedure that exists at the current server.
- argument-list
- Identifies a list of values to be passed as parameters to the
procedure. The nth
unnamed argument
corresponds
to the nth parameter in the procedure.Each parameter defined (using CREATE PROCEDURE) as OUT must be specified as either a SQL-variable-name or a SQL-parameter-name.
A default cannot be specified for an OUT parameter. If
a default is used for an INOUT parameter, then the default expression
is used to initialized the parameter for input to the procedure. No
value is returned for this parameter when the procedure exits.When a procedure is called, arguments must be specified
for all parameters that are not defined to have a default value. When
an argument is assigned to a parameter using the named syntax, then
all the arguments that follow it must also be assigned using the named
syntax.Any references to date, time, or timestamp
special register values in the argument list will use one clock reading
for any default expressions and a separate clock reading for any references
in the explicit arguments.The number of arguments specified must be the same as the number of parameters of a procedure defined at the current server with the specified procedure-name.
The application requester assumes all parameters that are variables are INOUT parameters. All parameters that are not variables are assumed to be input parameters. The actual attributes of the parameters are determined by the current server.
- parameter-name
- Name of the parameter to which the argument value is assigned.
The name must match a parameter name defined for the procedure. Named
arguments correspond to the same named parameter regardless of the
order in which they are specified in the argument list. When an argument
is assigned to a parameter by name, all the arguments that follow
it must also be assigned by name.
- A named argument must be specified only one time (implicitly or
explicitly).
- Named arguments are not allowed on a call to a procedure that
was not defined using a CREATE PROCEDURE statement.
- expression
- An expression of the type described in Expressions, that does not include an aggregate function or column name.
- DEFAULT
- Specifies
the default as defined in the CREATE PROCEDURE statement is used as
an argument to the procedure; otherwise the NULL value is used.
- NULL
- Specifies a null value as an argument to the procedure.
Notes
Rules for arguments to OUT and INOUT parameters: Each OUT or INOUT parameter must be specified as an SQL parameter or SQL variable.
Special registers: The initial value of a special register in a procedure is inherited from the caller of the procedure. A value assigned to a special register within the procedure is used for the entire SQL procedure and will be inherited by any procedure subsequently called from that procedure. When a procedure returns to its caller, the special registers are restored to the original values of the caller.
Related information: See CALL for more information.
Example
Call procedure proc1 and pass SQL variables as parameters.
CALL proc1(v_empno, v_salary)