1.5.3 SQL parameters and SQL variables

Organization of this subsection
(1) Format
(2) Function
(3) Data type

(1) Format

[[authorization-identifier.] routine-identifier.] SQL-parameter-name

[statement-label.] SQL-variable-name

(2) Function

(a) SQL parameters

SQL parameters are parameters for a routine that is declared in a procedure definition or a function definition.

When a routine is called by a CALL statement or a function call, an SQL parameter is a variable that permits values to be passed and received between the UAP or routine containing the CALL statement or function call and the routine that is called.

The functions that can be performed by SQL parameters depend on the parameter mode that is specified when the routine is declared:

How to specify an SQL parameter differs depending on whether it is specified in a routine coded in SQL or in an external routine coded using tools other than SQL, as follows:

When values are passed or received between a UAP and a routine, the CALL statement in the UAP specifies the embedded variables, indicator variables, or ? parameters in the function call.

(b) SQL variables

SQL variables can be used to pass and receive data between SQL statements in an SQL routine or between a table and an SQL routine. SQL variables can be declared in a compound statement in an SQL routine and can be referenced in the compound statement in which they are declared.

The null value can be stored in SQL parameters and SQL variables; the null value cannot be stored in embedded variables. For this reason, it is not necessary to use an indicator variable or to place a colon (:) before the name of the variable.

(3) Data type

SQL parameters and SQL variables specify the data type of the SQL. For an explanation of SQL data types, see 1.2 Data types.