Rules for Using the PREPARE Statement In an SQL Procedure
The following rules apply to using the PREPARE statement in an SQL procedure definition.
The PREPARE statement prepares the dynamic cursor SQL statement for execution.
You can use the PREPARE statement only for procedures that return result sets.
The SQL statement name specified by a PREPARE statement must be a standard SQL identifier.
The system passes PREPARE statements to the Parser for syntax checking.
If there is a syntax error, the request aborts and the statement return a syntax exception
error to the requestor.
If a PREPARE statement references a statement name in a dynamic DECLARE CURSOR statement,
then the statement must be a dynamic cursor SELECT statement.
If it is not, the system raises a SQLSTATE ‘07005’ error, meaning that the prepared
statement is not a cursor specification.
Whether specified as a string expression or as a variable, the dynamic SQL statement
text can be as long as 64 kbytes.
This maximum includes SQL text, USING data, and CLIv2 parcel overhead.
You can specify an OPEN … USING statement only for a cursor that returns result sets.
You cannot specify multistatement requests as part of a PREPARE statement.
A dynamic SQL statement can include either parameter markers or placeholder tokens
(the QUESTION MARK character) where any literal, particularly a SQL variable, reference
The only exception to this is that neither parameter markers nor placeholder tokens
can be specified in the select list.
The system supplies values to the statement by means of the USING clause of the OPEN
You cannot perform a PREPARE statement as a standalone dynamic SQL statement.
A maximum of 15 dynamic SQL statements is permitted in any procedure definition.
See SQL Stored Procedures and Embedded SQL for details.