SQL/MP Programming Manual for COBOL

Dynamic SQL Operations
HP NonStop SQL/MP Programming Manual for COBOL—529758-003
10-6
Using Parameters
When you prepare a statement, the database environment generates an
access plan and a description of the result set. Preparation is useful if you want
to execute a statement multiple times with the previously generated access
plan to minimize processing overhead.

The EXECUTE statement executes a previously prepared dynamic SQL
statement. You can use EXECUTE for any DDL, DML, or DCL statement
except SELECT. (Use a cursor to process a SELECT statement.)
Applications must process SELECT and nonSELECT statements differently. You do
not necessarily know what type of statement your program is processing. The
statement could, for example, be a SELECT, UPDATE, or DELETE statement. To
determine whether a statement is a SELECT statement, check the SQLSA. SELECT
and cursor statements always have at least one returned value.
This example functions the same as the example in Figure 10-1 on page 10-3, except
that it uses the PREPARE and EXECUTE statements instead of EXECUTE
IMMEDIATE:
(define the host variable)
DISPLAY "Enter statement to be executed:".
* User enters INSERT statement here
ACCEPT VAL of STATEMENT-BUFFER
(determine length of VAL)
EXEC SQL PREPARE S1 FROM :STATEMENT-BUFFER END-EXEC.
...
EXEC SQL EXECUTE S1 END-EXEC.
(Insertion performed)
Using Parameters
A parameter is an SQL identifier that serves as a place holder in a dynamic SQL
statement for a value substituted when the statement executes. (You can also use a
parameter with SQLCI.) You use parameter markers, with names preceded by question
marks (?), in place of each parameter. For example:
SELECT * FROM MONTHLY_PAYROLL WHERE NAME = ?NM.
The SQL statement is compiled without the actual input values, which are substituted
for the parameter when the SQL statement executes. For the syntax for a parameter,
see the SQL/MP Reference Manual.
Input Parameters
A dynamic SQL statement can contain input parameters, which allow your program to
construct SQL statements at run time. Input parameters convey data from the host
program to the SQL statement. They might denote criteria to be used in a WHERE
clause, values to be inserted into the database, or values used to update or delete
database records.