15.00 - Rules for Using the PREPARE Statement In an SQL Procedure - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Teradata Database
Release Number
Content Type
Programming Reference
Publication ID
English (United States)

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 is legal.
  • 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.
  • See SQL Stored Procedures and Embedded SQL for details.