Restricted Statements in Multistatement Prepared Objects

The following types of statements are also not valid in a multistatement prepare:

In general, you cannot use the SELECT statement in a multistatement prepare. The only form of the SELECT statement allowed in a multistatement prepare is a SELECT statement with an INTO temporary table clause.

Using Prepared Statements for Efficiency

To increase performance efficiency, you can use the PREPARE statement and an EXECUTE statement in a loop to eliminate overhead that redundant parsing and optimizing cause. For example, an UPDATE statement that is located within a WHILE loop is parsed each time the loop runs. If you prepare the UPDATE statement outside the loop, the statement is parsed only once, eliminating overhead and speeding statement execution. The following example shows how to prepare an ESQL/C statement to improve performance:

EXEC SQL BEGIN DECLARE SECTION;
   char disc_up[80];
   int cust_num;
EXEC SQL END DECLARE SECTION;
main()
{
   sprintf(disc_up, "%s %s","update customer ",
      "set discount = 0.1 where customer_num = ?");
   EXEC SQL prepare up1 from :disc_up;
   while (1)
      {
      printf("Enter customer number (or 0 to quit): ");
      scanf("%d", cust_num);
      if (cust_num == 0)
         break;
      EXEC SQL execute up1 using :cust_num;
      }
}

3Like the SQL statement cache, prepared statements can reduce 3how often the same query plan is reoptimized, thereby conserving resources 3in some contexts. The section Prepared Statements and the Statement Cache discusses 3the use of prepared DML statements, cursors, and the SQL statement cache as 3combined or alternative techniques for improving query performance.