SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
P-13
Considerations—Parameters
TYPE AS { DATETIME [start-dt TO] end-dt }
{ DATE }
{ TIME }
{ TIMESTAMP }
{ INTERVAL start-dt }
{ [ (start-field-precision) ] }
{ [ TO end-dt ] }
tells SQL to expect the value entered for the parameter to be a value of the
specified date-time or INTERVAL data type. (You cannot use TYPE AS to specify a
character or numeric data type for a parameter. Use the CAST function instead.)
A value for a parameter declared with a TYPE AS clause must have a character
data type and must be associated with a single-byte character set or the
UNKNOWN character set.
For example, This statement insert the character value in ?BIRTHDAY into a table
as a DATE value:
SET PARAM ?BIRTHDAY "1989-07-31";
INSERT INTO =EMPLOYEES (BIRTHDATE)
VALUES (?BIRTHDAY TYPE AS DATE);
If the name of a DATETIME or INTERVAL parameter occurs more than once in a
single SQL statement, each occurrence must include an identical TYPE AS clause.
Considerations—Parameters

You can use an unnamed parameter only in a prepared DML statement. The
unnamed parameter—a question mark (?) by itself—is always a distinct parameter
even when it occurs multiple times in a statement. When you execute a statement
with unnamed parameters, the position of the parameters indicates which values to
use for which parameters.
For example, this statement 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;

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.