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

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

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.