SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
E-10
Examples—EXECUTE
Examples—EXECUTE

This example uses PREPARE and EXECUTE to compile the statement stored in
the variable :DYNSTMT and execute it using parameter values stored in the
variables :PARTNUM, :PRICE, and :PARTDESC:
EXEC SQL
PREPARE OPERATION FROM :DYNSTMT;
EXEC SQL
EXECUTE OPERATION USING :PARTNUM, :PRICE, :PARTDESC;

This SQLCI example uses PREPARE to compile a statement once, then executes
the statement multiple times with different parameter values:
PREPARE FINDEMP FROM "SELECT * FROM PERSNL.EMPLOYEE"
&"WHERE SALARY > ? AND JOBCODE = ? ";
EXECUTE FINDEMP USING 30000, 200;
EXECUTE FINDEMP USING 40000, 100;

This SQLCI statements use CURRENT_TIMESTAMP and
COMPUTE_TIMESTAMP in EXECUTE USING clauses as values for both
LARGEINT and TIMESTAMP fields. Notice how the prepared statement uses
parameter ?T directly for the type LARGEINT field but converts it for the type
TIMESTAMP field.
CREATE TABLE DEMO (DEMOKEY NUMERIC,
JDATE LARGEINT, DTDATE TIMESTAMP, PRIMARY KEY DEMOKEY);
PREPARE MYSTMT FROM
"INSERT INTO DEMO VALUES (?K, ?T, CONVERTTIMESTAMP(?T) )";
EXECUTE MYSTMT USING ?K=1, ?T=CURRENT_TIMESTAMP;
EXECUTE MYSTMT USING ?K=2, ?T=COMPUTE_TIMESTAMP (10/13/93);

This SQLCI statements set parameter values with the SET PARAM command but
override one of the parameter values with a value in the USING clause of the
EXECUTE statement:
VOLUME PERSNL;
PREPARE NEWJOB FROM "INSERT INTO JOB VALUES (?CODE, ?DESC)";
SET PARAM ?CODE 950, ?DESC "TECHNICIAN";
EXECUTE NEWJOB USING ?DESC = "SR. TECHNICIAN";

This SQLCI statements use both SET PARAM and the USING clause of the
EXECUTE statement to supply parameters for a prepared SELECT.
Notice that the first two EXECUTE statements use the part number from SET
PARAM and use positional notation to provide a supplier number (the first
parameter) on the EXECUTE. The third EXECUTE uses both positional and
named notation to supply parameter values. The fourth EXECUTE uses the
?PNUM value from the SET PARAM (which was overridden, but not changed by
the using clause in the third EXECUTE).
PREPARE FINDSUP FROM "SELECT * FROM INVENT.PARTSUPP"
&" WHERE PARTNUM = ?PNUM AND SUPPNUM = ?";
SET PARAM ?PNUM 4103;
EXECUTE FINDSUP USING 6;