Where to Use Dynamic Parameters

Use a dynamic parameter as you would a constant in an expression in the DML operations listed below:

Table 4-1 Where to Use Dynamic Parameters

DML Operation	Clause
INSERT	VALUES WHERE, HAVING ^[1]
BULK INSERT	VALUES
UPDATE	SET
	WHERE
DELETE	WHERE
SELECT	WHERE, HAVING
EXECUTE	INPUT, OUTPUT
^[1]In the case of an INSERT statement, dynamic parameters can be used in the WHERE or HAVING clause of a Type 2 INSERT.

See the ALLBASE/SQL Reference Manual chapter, "Expressions," for more information regarding constants in expressions.

Restrictions

The examples below are shown to clarify the conditions under which dynamic parameters cannot be used. The following locations are not valid:

In any select list.
In any statement that is not dynamically preprocessed with the PREPARE statement.
As both operands of a single, arithmetic operator or comparison operator. The following example is not valid:
SELECT * FROM PurchDB.Parts WHERE SalesPrice > (? * ?)

As the operand of a minus sign or a null predicate. The following examples are not valid:

   INSERT INTO PurchDB.OrderItems VALUES (-?,?,?)



   UPDATE PurchDB.Parts

      SET SalesPrice = ?

    WHERE ItemNo = ?

       OR (ItemNo IS NULL AND ? IS NULL)

As the entire argument of an aggregate function. The following example is not valid:

   SELECT * FROM PurchDB.Orders

        GROUP BY PartNumber

          HAVING MAX(?) > 543

As the parameter of a NULL predicate. For example, the following is not valid:

   SELECT * FROM PurchDB.Parts

           WHERE ItemNo = ? 

              OR (ItemNo IS NULL AND ? IS NULL)

As both the first and second expressions of a BETWEEN predicate. The following example is not valid:
SELECT * FROM PurchDB.Orders WHERE ? BETWEEN ? AND 100
As both the expression and the first parameter of an IN predicate. The following example is not valid:
SELECT * FROM PurchDB.Orders WHERE ? IN (?, 4000, 5000, 6000)

Where to Use Dynamic Parameters

Technical documentation

» Table of Contents

» Index

Restrictions