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                                                      | |
      '---+-SQL-variable-name--------------------------------+-+-'
          +-SQL-parameter-name-------------------------------+
          +-constant-----------------------------------------+
          +-NULL---------------------------------------------+
          +-special-register---------------------------------+
          +-DLVALUE--(--arguments--)-------------------------+
          '-cast-function-name--(--+-SQL-variable-name--+--)-'
                                   +-SQL-parameter-name-+
                                   '-constant-----------'
 

Description

label

Specifies the label for the CALL statement. The label name cannot be the same as another label within the same scope. For more information, see Labels.

procedure-name

Identifies the procedure to call. The procedure-name must identify a procedure that exists at the current server.

argument-list

Specifies the arguments of the procedure. The number of arguments specified must be the same as the number of parameters defined by that procedure.

SQL-variable-name

Specifies an SQL variable as an argument to the procedure.

SQL-parameter-name

Specifies an SQL parameter as an argument to the procedure.

constant

Specifies a constant as an argument to the procedure.

NULL

Specifies the null value as an argument to the procedure.

special-register

Specifies a special register as an argument to the procedure.

DLVALUE(arguments)

Specifies the value for the parameter is the value resulting from a DLVALUE scalar function. A DLVALUE scalar function can only be specified for a DataLink parameter. The DLVALUE function requires a link value on insert (scheme, server, and path/file). The first argument of DLVALUE must be a constant, variable, or a typed parameter marker (CAST(? AS data-type)). The second and third arguments of DLVALUE must be constants or variables.

cast-function-name

This form of an argument can only be used with parameters defined as a distinct type, BINARY, VARBINARY, BLOB, CLOB, DBCLOB, DATE, TIME or TIMESTAMP data types. The following table describes the allowed uses of these cast-functions.

Parameter Type	Cast Function Name
Distinct type N based on a BINARY, VARBINARY, BLOB, CLOB, or DBCLOB	BINARY, VARBINARY, BLOB, CLOB, or DBCLOB *
Distinct type N based on a DATE, TIME, or TIMESTAMP	DATE, TIME, or TIMESTAMP *
BINARY, VARBINARY, BLOB, CLOB, or DBCLOB	BINARY, VARBINARY, BLOB, CLOB, or DBCLOB *
DATE, TIME, or TIMESTAMP	DATE, TIME, or TIMESTAMP *
Notes: * The name of the function must match the name of the data type (or the source type of the distinct type) with an implicit or explicit schema name of QSYS2.

constant

Specifies a constant as the argument. The constant must conform to the rules of a constant for the source type of the distinct type or for the data type if not a distinct type. For BINARY, VARBINARY, BLOB, CLOB, DBCLOB, DATE, TIME, and TIMESTAMP functions, the constant must be a string constant.

SQL-variable-name

Specifies an SQL variable as the argument. The SQL variable must conform to the rules of a constant for the source type of the distinct type or for the data type if not a distinct type.

SQL-parameter-name

Specifies an SQL parameter as the argument. The SQL parameter must conform to the rules of a constant for the source type of the distinct type or for the data type if not a distinct type.

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)