SQL Programming Manual for TAL

Host Variables and Parameters
HP NonStop SQL Programming Manual for TAL—527887-001
2-24
Using a Parameter List
TYPE AS ...
indicates that NonStop SQL should treat the value entered for the parameter as a
date-time or INTERVAL value. A parameter that represents a date-time or
INTERVAL value must have a character data type.
Using a Parameter List
To ensure a one-to-one correspondence between a parameter list and the host
variables that you use to supply values for the parameters, use unnamed parameters.
If duplicate parameter names appear in a statement, the names require a value for
only the first occurrence, and the duplicate occurrences receive the same value.
Suppose that the following UPDATE statement is stored in the host variable
:UPDATE^STMT:
UPDATE table SET col1 = ?a, col2= ?a, col3 = ?b
A PREPARE statement prepares the statement in the host variable :UPDATE^STMT:
PREPARE exec^stmt FROM :update^stmt
To supply values for the UPDATE statement at run time, the program uses the two host
variables :HOST^VAR1 and :HOST^VAR2 :
EXECUTE exec^stmt USING :host^var1, :host^var2
The value stored in HOST^VAR1 is used for both instances of the parameter named
?A. The value stored in :HOST^VAR2 is used for the parameter named ?B. If you use
three host variables, NonStop SQL uses the value in the first host variable for both
occurrences of parameter ?A. The value in the second host variable is used for
parameter ?B, and the value in the third host variable remains unused. For example, in
this statement:
EXECUTE exec^stmt USING :host^var1, :host^var2, :host^var3;
NonStop SQL uses the value in :HOST^VAR1 for both occurrences of parameter ?A
and the value in :HOST^VAR2 for parameter ?B. The value in :HOST^VAR3 is ignored.
Caution. If you use the same parameter name more than once in a statement, NonStop SQL
gives each duplicate occurrence of the parameter the same data type, length, and other
attributes as the first occurrence.Thus, data can be lost in some cases.
For example, during the execution of an INSERT statement, a parameter gets the same data
type and attributes as the column into which the parameter’s value is first inserted. If the
parameter value is truncated to fit into the column, the values of any duplicate occurrences of
the parameter are also truncated, even if a column is large enough to hold the complete value.