PREPARE | Teradata Vantage - PREPARE - Advanced SQL Engine - Teradata Database

Purpose

Prepares the dynamic DECLARE CURSOR statement to allow the creation of different result sets. Allows dynamic parameter markers.

Invocation

Executable.

Stored procedure only.

Syntax

PREPARE statement_name FROM { 'statement_string' | statement_string_variable } ;

statement_name

the same identifier as statement_name in a DECLARE CURSOR statement.

statement_string

the SQL text that is to be executed dynamically.

statement_string must be enclosed within apostrophes.

statement_string_variable

the name of an SQL local variable, or an SQL parameter or string variable, that contains the SQL text string to be executed dynamically.

This element should be a CHAR/VARCHAR variable less than 32000 characters.

ANSI Compliance

PREPARE is ANSI/ISO SQL:2011-compliant.

Authorization

None.

Rules

• The Parser checks the syntax of the PREPARE statement. If there is a syntax error, the system returns a syntax exception.
• You cannot execute PREPARE as a dynamic SQL statement.
• The statement must be a dynamic cursor SELECT statement. If this is not the case, the system returns '07005' dynamic SQL error, prepared statement not a cursor specification.
• The dynamic SQL statement text can be as long as 64 Kbytes (including SQL text, USING data, and parcel overhead).
• You cannot specify multistatement requests.
• The dynamic SQL statement can include parameter markers or placeholder tokens (the question mark), where any literal reference, particularly an SQL variable, is legal except in the select list.
• The USING clause of the OPEN statement supplies values to the statement.

Example: Using PREPARE

CREATE PROCEDURE abc (IN data1v VARCHAR(10), IN data2v VARCHAR(10) )
   DYNAMIC RESULT SETS 1
  BEGIN
     DECLARE sql_stmt1 VARCHAR(100);
     DECLARE sales DECIMAL(8,2);
     DECLARE item INTEGER;
     DECLARE cstmt CURSOR WITH RETURN ONLY FOR stmt1;
     SET sql_stmt1 = 'SELECT  T1.item, T1.sales FROM T1 WHERE'  data1v
                     || '= store_name AND '   || data2v || '= region;';
     PREPARE stmt1 FROM sql_stmt1;
     OPEN cstmt;
  END;

Example: Using PREPARE with Parameter Markers

In this example, the PREPARE statement is written using parameter markers:

SET sql_stmt1 = 'SELECT  T1.item, T1.sales FROM T1 WHERE ?'
                '= store_name AND ? = region;';
PREPARE stmt1 FROM sql_stmt1;
OPEN cstmt USING data1v, data2v;

Related Topics

For use of the PREPARE statement in embedded SQL, see PREPARE.