Writing an SQL Program › Local Variables and Routine Parameters

Local Variables and Routine Parameters

Local variables and routine parameters are program variables of SQL routines. These variables can be used as any program variable and are necessary for the SQL routine to receive data from the database and to modify data in the database. In addition to their role as program variables, routine parameters are mainly used to pass input values from and output values to the invoker of the SQL routine.

Local variables are defined in the DECLARE statement of a compound SQL statement. Routine parameters are defined in the parameter-definition clause of the CREATE PROCEDURE or CREATE FUNCTION statements.

How Local Variables and Routine Parameters Are Used

Local variables and routine parameters are used as follows:

• Receive column values specified in a SELECT or FETCH statement
• Supply column values specified in an UPDATE statement, INSERT statement, or other statements containing a search condition
• Supply information for dynamically executed statements

  Note: For more information about dynamically executed statements, see Using Dynamic SQL.

Local Variable Example

In the following example, DEPT_ID, EMP_LNAME, and EMP_ID are local variables defined in a compound statement with label MAIN_BLOCK. DEPT_ID and EMP_LNAME receive column values and EMP_ID supplies a column value used in the search condition of the statement:

SELECT EMPLOYEE.DEPT_ID, EMPLOYEE.EMP_LNAME INTO MAIN_BLOCK.DEPT_ID,
MAIN_BLOCK.EMP_LNAME
 FROM EMPLOYEE
WHERE EMPLOYEE.EMP_ID = MAIN_BLOCK.EMP_ID;

Null Value

A null value is the absence of a value and is not the same as spaces or numeric zeros which are actual values. Local variables and routine parameters are always nullable. However, as these are SQL variables, null support is built-in and null indicators must not be used.

Note: For more information, see the CA IDMS SQL Reference Guide.