SQL Programming Manual for TAL

Host Variables and Parameters

HP NonStop SQL Programming Manual for TAL—527887-001

2-25

Using Parameters in Loops

Parameters are often used when a dynamic SQL statement is executed repeatedly

with different input values. In this example, a dynamic SQL statement uses a

parameter. Because the user of this program can enter any SQL statement, the

program does not have compile-time information about the statement. The TACL

DEFINE named =PARTS represents the PARTS table.

1. A user enters this SQL statement from a terminal:

UPDATE =parts SET price = ?p

2. The program copies the statement into the host variable named :INTEXT.

3. The program uses the PREPARE and DESCRIBE INPUT statements to get a

description of the parameter in the input SQLDA (IN^SQLDA) and to get the name

of the parameter in the input names buffer (I^NAMESBUF). The prepared

statement is named EXEC^STMT.

EXEC SQL PREPARE exec^stmt FROM :intext;

EXEC SQL DESCRIBE INPUT s1 INTO :in^sqlda

NAMES INTO :i^namesbuf;

4. The program enters a loop to prompt the user to supply values for successive

execution of the statement:

! Beginning of loop

! Prompt the user for a value using the parameter

! name from the names buffer

...

! Store the value in a value buffer pointed to by in^sqlda

! Execute the statement using each successive value:

EXEC SQL EXECUTE s1 USING DESCRIPTOR :in^sqlda;

! End of loop

Using Indicator Parameters

A program uses an indicator parameter to show that a null value was entered for a

parameter. The indicator parameter follows the parameter in the SQL statement. For

example:

INSERT INTO =employee VALUES (1000, ?p INDICATOR ?i );

If a user enters a null value for ?P, the program should set ?I to a value less than zero.

If a user enters a nonnull value, the program should set ?I to 0. Both ?P and ?I are in

the names buffer to allow prompting the user for a null value.

For more information and examples for using parameters in dynamic SQL statements,

see Section 7, Dynamic NonStop SQL Operations