NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
P-14
Considerations—Parameters
with unnamed parameters, the position of the parameters indicates which values to
use for which parameters.
For example, the following statements average the salaries of employees in
departments with numbers between 1000 and 2000, and between 5000 and 6000:
PREPARE AVGSAL FROM
"SELECT AVG (SALARY) FROM PERSNL.EMPLOYEE
WHERE DEPTNUM BETWEEN ? AND ?";
EXECUTE AVGSAL USING 1000, 2000;
EXECUTE AVGSAL USING 5000, 6000;
•
Named parameters
You can use a named parameter only in a prepared DML statement or in an SQLCI
command file. Each occurrence of the same parameter name within a statement or
an SQLCI session refers to the same parameter. There is no way to qualify a
parameter name.
If you use the same parameter name more than once in a single statement, SQL
considers each reference to point to the same parameter and assigns each occurrence
the same data type, length, and other attributes as the first occurrence.
Assigning a value to the first occurrence of a parameter in the statement
automatically assigns a value to the other occurrences also. For example, assume a
statement uses five parameters—two named A, two unnamed, and one named B—
ordered as follows:
?A, ?, ?B, ?, ?A
Executing the statement requires only four values, for example:
EXECUTE USING 10, 20, 30, 40;
because SQL assigns the first value (10) to each of the parameters named A. SQL
ignores any additional values in the EXECUTE USING statement, because four
values are sufficient to assign values to the parameters in the statement.
Using the same parameter name more than once in a single statement should be
done carefully, since it can lead to loss of data in certain cases. For example, during
the execution of an INSERT statement, a parameter is assigned the same data type
and attributes as the column into which the parameter's value is first inserted. If SQL
truncates the parameter value to fit into the column, other occurrences of the
parameter also receive the truncated value, even if the columns for those parameters
are large enough to hold the complete value.
•
Default type assignment for parameters
The data type of a parameter is derived from the data type of the target column, as
follows: