Identifiers › Expansion of Statement-name

Expansion of Statement-name

The expanded parameters of statement-name represent a dynamically-prepared statement.

Syntax

Expansion of statement-name

►►──┬── static-statement-name ────┬───────────────────────────────────────────►◄
    └── extended-statement-name ──┘

Expansion of static-statement-name

►►── statement-name ──────────────────────────────────────────────────────────►◄

Expansion of extended-statement-name

►►──┬─────────────┬───┬─ 'cursor-name' ────┬──────────────────────────────────►◄
    ├─── LOCAL ◄ ─┤   ├─ :host-variable ───┤
    └─── GLOBAL ──┘   ├─ local-variable ───┤
                      └─ routine-parameter ┘

Parameters

statement-name

Specifies the name of the statement as an identifier.

'statement-name'

Specifies the name of the statement as a literal whose value must conform to the rules for an identifier.

:host-variable

Specifies the name of the statement as a host-variable whose value must conform to the rules for an identifier.

local-variable

Specifies the name of the statement as a local-variable whose value must conform to the rules for an identifier.

routine-parameter

Specifies the name of the statement as a routine-parameter whose value must conform to the rules for an identifier.

LOCAL/GLOBAL

Specifies the scope of the associated statement name:

• LOCAL indicates that the statement can be referenced only from within the program where it is prepared.
• GLOBAL indicates that the statement can be referenced from any program executing within the same SQL transaction.

The default is LOCAL.

Usage

Static Versus Extended Statement Names

A static statement name is one coded as a simple identifier. The following PREPARE statement assigns the static name "select1" to the statement being prepared:

PREPARE select1 from :select1-text

An extended statement name is one coded either as a literal, a host-variable, a local-variable, or a routine-parameter.

MOVE 'SELECT1' to statement-nam
PREPARE :statement-nam FROM :select1-text

If a statement is prepared using a static name, it must be referenced using a static name; similarly, if it is prepared using an extended name, it must be referenced using an extended name that has the same scope option.

Uniqueness of Statement Names

Static and extended names do not have to be unique with respect to each other. If two statements are assigned the same value for a name, they are considered two separate statements provided that either:

• One of the names is static while the other is extended.
• Both of the names are extended, but they have different scopes, as indicated by their LOCAL/GLOBAL parameter.

Example

Static-statement-name

The following PREPARE statement creates a statement using a static statement name of S1. It is referenced within the subsequent DESCRIBE statement:

EXEC SQL
  PREPARE S1 FROM :TEXT
END-EXEC
EXEC SQL
  DESCRIBE S1 USING DESCRIPTOR SQLDA
END-EXEC

Extended-statement-name

The following PREPARE statement creates a local statement using an extended statement name of S1. It is then referenced in the subsequent DESCRIBE statement:

EXEC SQL
  PREPARE 'S1' FROM :TEXT
END-EXEC
EXEC SQL
  DESCRIBE 'S1' USING DESCRIPTOR SQLDA
END-EXEC

Note: Even though S1 is used as the statement name in both of the above examples, two separate statements are created: one with a static name of S1 and one with an extended name of S1.

Global Extended-statement-name

The following PREPARE statement creates a global statement using an extended statement name whose value is not known until runtime. In this case, the value 'S2' is moved to the host variable before the statement is executed and will be the name of the statement created:

MOVE 'S2' TO :SNAME
EXEC SQL
  PREPARE GLOBAL :SNAME FROM :TEXT
END-EXEC

Since this is a global statement, it can be referenced in a different program than the one where the PREPARE statement appears. For example, the following DESCRIBE statement might be contained in a different program:

EXEC SQL
  DESCRIBE GLOBAL 'S2'
END-EXEC

Note: It does not matter that in one case the name of the statement is supplied through a host-variable and in the other it is specified as a literal. They both refer to the global statement S2.