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.
|
|
| 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 same applies when deleting entire PPI partitions. |
- Using the SQL PREPARE statement
See Rules for Using the PREPARE Statement In an SQL Procedure and Teradata Vantage™ - SQL Stored Procedures and Embedded SQL, B035-1148.
Note that dynamic SQL written in this form can specify SELECT and SELECT INTO statements that return rows. See Dynamic Result Sets and Rules and Limitations for Dynamic Result Sets.
- 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, Unsupported DML Statements in SQL Procedures, and Supported DCL Statements in SQL Procedures.
Note that dynamic SQL written in this form cannot specify SELECT and SELECT INTO statements that return rows.
You can specify only one dynamic request per multistatement request.