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

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1184
lifecycle
latest
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.