Values and Value Expressions › Expansion of Routine-parameter

Expansion of Routine-parameter

The expanded parameters of routine-parameter identify routine parameters of an SQL routine.

Syntax

Expansion of routine-parameter

►─┬──────────────────────────────────────────────┬── parameter-name ──────────►◄
  └─┬───────────────────┬─────── routine-name. ──┘
    └─ schema. ─────────┘

Parameters

schema

Specifies the schema with which the SQL routine identified by routine-name is associated.

routine-name

Specifies the name of the SQL routine in which the routine parameter identified by routine-parameter is defined.

parameter-name

Identifies a parameter of an SQL routine.

Usage

Referencing Routine Parameters

Routine parameters can only be referenced within the body of the SQL routine in which they are defined. A routine parameter is global to the SQL routine. It can be referenced anywhere in the body of the routine.

Avoiding Ambiguous References

The name of a routine parameter can be the same as the name of a local variable, a column, or another schema-defined entity such as a table. To avoid ambiguity when referencing these objects, qualification can be used as follows:

• A local variable can be qualified with the label of the compound statement in which it is declared.
• A routine parameter can be qualified with its associated schema and routine name.
• A column can be qualified with its schema and table name.
• Other schema-defined objects can be qualified with the name of the schema in which they are defined.

Resolving Ambiguous References

If a name is not qualified and more than one object has the specified name, CA IDMS uses the following precedence rules to resolve the ambiguous reference:

• If a local variable with a matching name has been declared within the compound statement in which the reference occurs, the reference is to the local variable. If more than one such variable is declared, the reference is to the variable declared in the innermost compound statement containing the reference.
• If a parameter of the routine in which the reference occurs has a matching name, the reference is to the routine parameter.
• Otherwise, the reference is treated as a reference to a schema-defined object. For information about how such a reference is resolved, see Resolving References to Entities in Schemas.

Note: In the SQL standard, an unqualified reference would be to the object with innermost scope.

Example

In the following SQL procedure, three routine parameters, TITLE, P_EMP_ID, and P_LAST_NAME are defined. The references to P_EMP_ID and P_LAST_NAME in the SELECT statement are qualified. The SET statement uses an unqualified reference to TITLE.

 set options command delimiter '++';
 create procedure SQLROUT.GETLNAME
   ( TITLE     varchar(10) with default
   , P_EMP_ID  NUMERIC(4)
   , P_LAST_NAME    varchar(25)
   )
     external name GETLNAME language SQL
 L_MAIN: begin not atomic

  select EMP_FNAME
    into SQLROUT.GETLNAME.P_LAST_NAME
    from DEMOEMPL.EMPLOYEE
   where EMP_ID = GETLNAME.P_EMP_ID;

   set TITLE = 'Success';
 end L_MAIN
 ++

 call SQLROUT.GETLNAME  ('?',2010)++
 *+
 *+ TITL:EP_EMP_ID   P_LAST_NAME
 *+ -----       --------   -----------
 *+ Success         2010   Cora