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

SQL Data Definition Language Detailed Topics

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