PREPARE Rules - Teradata Vantage

Teradata® VantageCloud Lake

Deployment

VantageCloud

Edition

Lake

Product

Teradata Vantage

Published

January 2023

ft:locale

en-US

ft:lastEdition

2024-12-11

dita:mapPath

phg1621910019905.ditamap

dita:ditavalPath

pny1626732985837.ditaval

dita:id

phg1621910019905

An SQLDA must 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 maximum length of a dynamic SQL statement text is 32 KB (including SQL text, USING data, and parcel overhead). For SQL string rules, see Teradata® Preprocessor2 for Embedded SQL Programmer Guide, B035-2446.

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

Language	statement_string
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 multiple-statement 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
- BEGIN TRANSACTION
- CHECKPOINT
- CLOSE
- COMMIT
- DESCRIBE
- ECHO
- END TRANSACTION
- EXECUTE
- EXECUTE IMMEDIATE
- FETCH
- LOGOFF
- LOGON
- OPEN
- POSITION
- PREPARE
- REWIND
- ROLLBACK
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 is either 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 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, 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 run as a dynamic SQL statement.

See DESCRIBE.