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
>>-+--------+--CALL--procedure-name--argument-list------------->< '-label:-' argument-list |--(--+----------------------------------------------+--)-------| | .-,----------------------------------------. | | V | | '---+----------------------+--+-expression-+-+-' '-parameter-name-- => -' +-DEFAULT----+ '-NULL-------'
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 initialize 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 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. If no default is defined for the parameter, 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)