Rules for Using the PREPARE Statement in an SQL Procedure | Teradata Vantage - 17.10 - Rules for Using the PREPARE Statement In an SQL Procedure - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Advanced SQL Engine
Teradata Database
Release Number
July 2021
English (United States)
Last Update
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.