PREPARE | Teradata Vantage - PREPARE - Advanced SQL Engine

Purpose

Prepares a dynamic SQL statement for execution and assigns a name to it.

Invocation

Executable.

Dynamic SQL.

Embedded SQL only.

Syntax

PREPARE statement_name
  [ INTO [:] descriptor_area
    [ USING { NAMES | ANY | BOTH | LABELS } ] ]
    [ FOR STATEMENT { statement_number | [:] numeric_variable } ]
  ]
  FROM { statement_string | [:] statement_string_variable }

statement_name: The name to be associated with the prepared statement.; statement_name must be a valid SQL identifier and must not be enclosed within apostrophes.
descriptor_area: Specifies the SQLDA to receive the descriptive information about the data returned when the prepared statement is executed.; You can specify descriptor_area in C programs as a name or as a pointer reference (*sqldaname) when the SQLDA structure is declared as a pointer.
statement_string: The text of the dynamic SQL statement or statements as a string expression.
statement_string_variable: The dynamic SQL statement text as a host variable.; The colon before statement_string_variable is optional.
statement_number: A valid integer literal that identifies the statement number within the request for which the descriptive information is requested
numeric_variable: A host variable of type INTEGER or SMALLINT that represents the statement number in the request for which the descriptive information is requested.; The preceding colon is optional.

ANSI Compliance

PREPARE is ANSI/ISO SQL:2011-compliant.

Authorization

None.

Preparing an SQL Statement

Using the syntax elements defined here, the process is as follows:

Declare the variable statement_string in the client application language.
Define statement_string as the literal character text of the SQL statement to be executed, still in the client application language.
Execute the PREPARE statement from within SQL, defining the host variable statement_string as the SQL variable statement_name.
PREPARE compiles the source code from statement_name into executable object code.
Execute the EXECUTE or EXECUTE IMMEDIATE statement for statement_name.
The database software posts return codes to SQLCODE and SQLSTATE.

Rules

An SQLDA should be defined whenever you use dynamic SQL.

statement_name cannot exceed 18 characters.

Whether specified as a string expression or as a host variable, the dynamic SQL statement text can be as long as 32 kbytes (including SQL text, USING data, and parcel overhead).

If specified as a host variable, the statement string must follow the rules for SQL strings for the client programming language.

IN this language …	statement_string is a …
COBOL	non-numeric literal.
C	non-numeric literal.
PL/I	character string expression.

The dynamic SQL statement text in statement_string can:

Be a single statement or a multistatement request
Incorporate an EXEC statement
Incorporate a data returning statement

If the statement string variable is varchar, the statement text cannot be longer than 64K.

The dynamic SQL statement cannot be:

Any of the following specific SQL statements:

ABORT	EXECUTE IMMEDIATE
BEGIN TRANSACTION	FETCH
CHECKPOINT	LOGOFF
CLOSE	LOGON
COMMIT	OPEN
CONNECT	POSITION
DESCRIBE	PREPARE
ECHO	REWIND
END TRANSACTION	ROLLBACK
EXECUTE

A Preprocessor2 declarative.

The dynamic SQL statement can include parameter markers, or placeholder tokens (the question mark), where any literal, particularly a host variable, reference is legal.

Values are supplied to the statement by means of the USING clause of the OPEN and EXECUTE statements.

Placeholders are typed and untyped.

A typed placeholder has its data type explicitly cast. For example, part_no is a typed placeholder in the following UPDATE statement.
```
UPDATE parts
SET part_no = (CAST(? AS INTEGER))
WHERE vendor_no = ?;
```
This action establishes that the data type of the variable at runtime will either be the type cast for the placeholder or one that is convertible to that type.
An untyped placeholder is one for which the data type is determined by its context.
For example, in the following statement, the type of the untyped placeholder in the WHERE clause is the same as that of vendor_no.
```
UPDATE parts
SET part_no = (CAST(? AS INTEGER)
WHERE vendor_no = ?;
```

Using placeholders within a CASE expression as the result of a THEN/ELSE clause is valid only when at least one other result in the CASE expression is not a placeholder or the NULL keyword.

The following uses of untyped placeholders are not valid:

As the operand of a monadic operator.
For example, + ? is not valid.
As both operands of a dyadic operator.
For example, ? + ? is not valid.
As both operands of a comparison operator.
For example, the following SELECT statement is not valid.
```
SELECT *
FROM table_name
WHERE ? = ?
```
If you were to replace either placeholder with 0+, the comparison becomes valid because such a value provides enough context to determine that the data type is numeric.

By extension, it is also true that placeholders cannot be used to denote corresponding fields in a comparison.

For example, the following comparison is not valid because the first value in each pair has an unknown type that cannot be determined from the context at PREPARE time:
```
(?,X) > (?,Y)
```
At the same time, the following comparison is valid because the types can be determined from the context at PREPARE time.
```
(?,X) > (Y,?)
```
As both operands in a POSITION function.
As the only operand in an UPPER function.
As the only operand in a LOWER function.
As either the second or third operand in a TRIM function.
As the FROM operand in an EXTRACT function.
As the first operand in a TRANSLATE function.
As the argument for any aggregate function.
As any component of the left hand operand of IS [NOT] NULL.
As the second component of either operand of an OVERLAPS comparison.
As solitary select item expressions.
For example, the following SELECT is not valid.
```
SELECT ? AS alias_name
FROM table_name;
```
The following SELECT statement is valid because the placeholder is used as part of an expression and not as the entire expression in the SELECT list.
```
SELECT 0 + ? AS alias_name
FROM table_name;
```

Executing a PREPARE statement with an INTO clause (and optionally a USING clause) is exactly equivalent to the following:

Executing the PREPARE statement without the INTO and USING clauses.
Executing a DESCRIBE statement for the prepared statement using the INTO and USING clauses.
PREPARE cannot be executed as a dynamic SQL statement.

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

SQL Stored Procedures and Embedded SQL