An EXECUTE USING statement

You can execute a parameterized non-SELECT statement (a non-SELECT that contains input parameters) with the EXECUTE...USING statement.

The following statements are parameterized non-SELECT statements:
  • A DELETE or UPDATE statement with input parameters in the WHERE clause
  • An UPDATE statement with input parameters in the SET clause
  • An INSERT statement with input parameters in the VALUES clause
  • An EXECUTE PROCEDURE statement with input parameters for its function arguments
Tip: You cannot use an input parameter as the procedure name for a user-defined procedure.
For example, the following UPDATE statement requires two parameters in its WHERE clause: 
EXEC SQL prepare upd_id from 
   'update orders set paid_date = NULL \
    where order_date > ? and customer_num = ?';
The USING clause lists the names of the host variables that hold the parameter data. If the input parameter values are stored in hvar1 and hvar2, your program can execute this UPDATE with the following statement: 
EXEC SQL execute upd_id using :hvar1, :hvar2;
The following steps describe how to handle a parameterized UPDATE or DELETE statement when the type and number of parameters are known at compile time:
  1. Declare a host variable for each input parameter that is in the prepared statement.
  2. Assemble the character string for the statement, with a question mark (?) placeholder for each input parameter. Once you have assembled the string, prepare it. For more information about these steps, see Assemble and prepare the SQL statement.
  3. Assign a value to the host variable that is associated with each input parameter. (The application might obtain these values interactively.)
  4. Execute the UPDATE or DELETE statement with the EXECUTE...USING statement. You must list the host variables that contain the input parameter values in the USING clause.
  5. Optionally, use the FREE statement to release the resources that were allocated with the prepared statement.
Important: If you do not know the number and data types of the input parameters in the prepared statement at compile time, do not use host variables with the USING clause. Instead, use a dynamic-management structure to specify input parameter values.

For more information about determining the number and types of input parameters, see Determine unknown input parameters.

For more information about the USING clause, see the entry for EXECUTE in the HCL OneDB™ Guide to SQL: Syntax.