15.00 - SQL Multistatement Request Support in SQL Procedures - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

SQL Multistatement Request Support in SQL Procedures

You can specify standard Teradata multistatement requests within the procedure body for procedures created in either ANSI or Teradata session modes. The feature has the same limitations as the standard multistatement requests submitted by means of BTEQ, embedded SQL, or other standard SQL statement entry paths (see SQL Fundamentals). Like other SQL multistatement requests, the system sends procedure multistatement requests to the AMPs in parallel.

The statements contained within the multistatement request are restricted to DML statements such as INSERT, UPDATE and DELETE. You cannot specify any other type of SQL statements within the block.

You cannot include a SELECT AND CONSUME statement within the same multistatement request or explicit transaction as a DELETE, MERGE, or UPDATE statement that operates on the same queue table.

If there are no errors in any of the statements within the multistatement request block, the system does not post activity results.

If multiple statements specify a completion condition, the system returns only the condition code for the first, which is also the only statement in the block that is handled by an exception handler.

If the request aborts, the system rolls back all SQL statements within it and, if one has been specified, passes control to an exception handler. If no exception handler has been specified, the procedure exits with the error.

The multistatement block can be composed of multiple individual semicolon‑separated DML statements or 1 dynamic DML statement. No other type of statement is valid.

The items in the following list are the only SQL statements that can be sent as part of a procedure multistatement request.

 
 

IF the session is in this mode …

THEN multiple INSERT … SELECT statements …

ANSI

use the fast path if the last statement in the request is a COMMIT statement.

A DELETE ALL statement uses the fast path if the next and last sequential statement in the multistatement request is a COMMIT statement.

The same applies when deleting entire PPI partitions.

Teradata

use the fast path if the multistatement request is an implicit transaction or an explicit transaction that terminates with an END TRANSACTION statement

A DELETE ALL statement uses the fast path if it is either of the following.

  • The last statement in the implicit request.
  • An explicit transaction terminated by an END TRANSACTION statement.
  • The same applies when deleting entire PPI partitions.

    The dynamic SQL feature allows you to build ad hoc SQL statements within a procedure application. The system can build a request in 1 of 2 ways.

  • Using the SQL PREPARE statement
  • See “Rules for Using the PREPARE Statement In an SQL Procedure” on page 470 and SQL Stored Procedures and Embedded SQL for details.

    Note that dynamic SQL written in this form can specify SELECT and SELECT INTO statements that return rows. See “Details About Dynamic Result Sets” on page 460 and “Rules and Limitations for Dynamic Result Sets” on page 464 for details.

  • Using the DBC.SysExecSQL procedure call for several semicolon‑separated SQL DML statements.
  • When multiple statements are created in this way, the system executes the entire set as 1 request.

    The set of SQL statements that is valid within a dynamic SQL request is the same as the valid set for static SQL (see “Supported DDL Statements in SQL Procedures” on page 467, “Unsupported DML Statements in SQL Procedures” on page 469, and “Supported DCL Statements in SQL Procedures” on page 470 for details).

    Note that dynamic SQL written in this form cannot specify SELECT and SELECT INTO statements that return rows.

    You can specify only 1 dynamic request per multistatement request.