The PREPARE and EXECUTE INTO statements

The only prepared SELECT statement that you can execute with the EXECUTE statement is a singleton SELECT. Your program must take the following actions:

1. Declare host variables to receive the values that the database server returns.

   For a prepared SELECT statement, these values are the select-list columns.

2. Assemble and prepare the statement.

   A prepared SELECT statement can contain input parameters in the WHERE clause.

3. Execute the prepared selection with the EXECUTE...INTO statement, with the host variables after the INTO keyword.

   If the SELECT statement contains input parameters, include the USING clause of EXECUTE.

With the INTO clause of the EXECUTE statement, you can still use the following features:

• You can associate indicator variables with the host variables that receive the select-list column values.
  Use the INDICATOR keyword followed by the name of the indicator host variable, as follows:

  EXEC SQL prepare sel1 from 
    'select fname, lname from customer where customer_num = 123';
  EXEC SQL execute sel1 into :fname INDICATOR :fname_ind,
     :lname INDICATOR :lname_ind;

• You can specify input parameter values.
  Include the USING clause of EXECUTE, as follows:

  EXEC SQL prepare sel2 from
    'select fname, lname from customer where customer_num = ?';
  EXEC SQL execute sel2 into :fname, :lname using :cust_num;

You are not required to prepare a singleton SELECT. If you do not need the benefits of a prepared statement, you can embed a singleton SELECT statement directly in your program, as shown in the following example:

EXEC SQL select order_date from orders where order_num = 1004;

The following figure shows how to execute the items_pct() SPL function (which Code for items_pct SPL function shows). Because this function returns a single decimal value, the EXECUTE...INTO statement can execute it.

EXEC SQL prepare exfunc_id from
   'execute function items_pct(\"HSK\")';
EXEC SQL execute exfunc_id into :manuf_dec;

You can use host variables for routine arguments but not the routine name. For example, if the manu_code variable holds the value "HSK", the following EXECUTE statement replaces the input parameter in the prepared statement to perform the same task as the EXECUTE in the preceding code fragment.

EXEC SQL prepare exfunc_id from
   'execute function items_pict(?)';
EXEC SQL execute exfunc_id into :manuf_dec using :manu_code;