SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
P-14
Considerations—Parameters
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:
?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, because 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.

The data type of a parameter is derived from the data type of the target column:

If the target column has a numeric data type, SQL handles the parameter as
DECIMAL(n), where n is the number of digits in the parameter value.

If the target column has a character data type and the target column has the
UNKNOWN character set associated with it, SQL handles the parameter as
CHAR(n), where n is the number of bytes in the parameter value.

If the target column has a character data type and the target column has a
character set other than UNKNOWN associated with it, SQL handles the data
type of the parameter as
CHAR(n) CHARACTER SET character-set-name
where character-set-name is the character set specified in the parameter
value and n is the number of quoted characters in the parameter value. In this
case, the parameter value must be a string literal.

If you omit the TYPE AS clause from a parameter in a date-time or INTERVAL
expression, SQL assigns data types to the parameters according to these rules:

The data type is INTERVAL if the parameter name is followed by a range of
fields and start-field-precision is specified.