Support Information for SQL Procedures | CREATE PROCEDURE | Teradata Vantage - 17.10 - SQL Multistatement Request Support in SQL Procedures - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
English (United States)

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 Teradata Vantage™ - SQL Fundamentals, B035-1141). 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.

  • ABORT
  • BEGIN TRANSACTION
  • COMMIT
  • DELETE
  • END TRANSACTION
  • INSERT
  • MERGE
  • ROLLBACK
  • SELECT INTO

    You cannot specify row-returning SELECT or SELECT INTO requests in dynamic SQL that is written using the DBC.SysExecSQL procedure.

  • UPDATE
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 one of the following ways: