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 statement returns a syntax exception error to the requestor.
- The statement must be a dynamic cursor SELECT statement if a PREPARE statement references a statement name in a dynamic DECLARE CURSOR statement.
Otherwise, the system returns an 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 is allowed.
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 statement.
- 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.
For more information, see Teradata Vantage™ - SQL Stored Procedures and Embedded SQL, B035-1148.