Multistatement Requests with Embedded SQL | Teradata Vantage - Multistatement Requests with Embedded SQL - Advanced SQL Engine - Teradata Database

SQL Stored Procedures and Embedded SQL

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-28
dita:mapPath
vqj1592443206677.ditamap
dita:ditavalPath
vqj1592443206677.ditaval
dita:id
B035-1148
lifecycle
previous
Product Category
Teradata Vantage™

Multistatement Requests Require Cursors

A multistatement request often returns a response having more than one row. Each statement in the request produces its own results (success/failure, activity count and so on), which are returned to the application program.

Because the results table for a multistatement request returns more than one response, you must declare a cursor to fetch and manipulate the results.

TO associate a … YOU must …
static multistatement request with a request cursor issue a DECLARE CURSOR statement for a request cursor.
dynamic multistatement request with a dynamic cursor use a PREPARE statement with the statement string containing a multistatement request.

The dynamic request is then associated with a dynamic cursor.

Using the FOR STATEMENT Clause With PREPARE and DESCRIBE

You can extend the syntax of PREPARE and DESCRIBE by using the FOR STATEMENT clause. FOR STATEMENT permits you to specify which of the statements in the multistatement request is to be described.

To describe all statements of a multistatement request, the DESCRIBE statement must be executed multiple times for each data returning statement within the request.

Even though the output SQLDA contains no column descriptions, it is always valid to DESCRIBE a non-data-returning statement.

Using SQLDA to Track Statement Status

In processing the output from a multistatement request, you must know the success or failure of each statement and when the output from one request ends and output from the next begins.

The mechanism described by the following table, which is similar to that used for single statement requests, provides a framework for achieving this.

WHEN … THEN …
the request cursor is opened SQLCODE in SQLCA is set to reflect the success of the first statement of the request or the failure (failure defined as an IMPLICIT ROLLBACK occurrence) of the request as an entity.

A failure condition overrides a success report.

If successful, the activity count is reported to the application in the third SQLERRD element in the SQLCA.

the statement is in error (error defined as a non-implicit ROLLBACK) the next FETCH returns the appropriate error code: SQLCODE in the SQLCA is < 0 and the error code in the first element of SQLERRD.
no rows are returned or the rows returned for a particular statement are exhausted SQLCODE is set to +100 on return from the FETCH, just as with a single statement request.
the application needs to position to the next (or any) statement of the request use the POSITION statement.

POSITION moves control to the output for the specified statement of the request and sets the SQLCA information based on the success or failure of the OPEN request.

The program can then use FETCH to retrieve the output of the statement.

the application needs to position to the beginning of all output for the request use the REWIND statement.

The REWIND statement is exactly equivalent to POSITION TO STATEMENT 1.

REWIND moves control to the output for the specified statement of the request and sets the SQLCA information based on the success or failure of the OPEN request.

The program can then use FETCH to retrieve the output of the statement.

you receive +100 SQLCODE for the current statement use POSITION or REWIND to access the results of another (or even the same) statement.

You do not need to wait until the +100 is received. You can issue POSITION or REWIND statements at any time.

Multistatement Request Example

An example of a multistatement request is shown in the following passages. The SQL prefixes and terminators are omitted. This example assumes successful completion of the statements in the request.

DECLARE curs CURSOR FOR
’SELECT ent1,ent2,ent3 FROM tabx;
UPDATE ...;SELECT entt FROM tabl’
    
OPEN curs {SQLCA gets first SELECT result}
WHENEVER NOT FOUND GOTO updstmt
    
selstmt1:
FETCH curs INTO :vara,:varb,:varc
.
.
GOTO selstmt1
    
updstmt:
WHENEVER NOT FOUND CONTINUE
POSITION curs TO STATEMENT 2 {SQLCA gets UPDATE
result}
FETCH curs
.
.
WHENEVER NOT FOUND GOTO reread
POSITION curs TO STATEMENT 3 {SQLCA gets second
SELECT result}
    
selstmt2:
FETCH curs INTO :vars
.
.
GOTO selstmt2
   
reread:
REWIND curs {SQLCA gets first SELECT result}
WHENEVER NOT FOUND GOTO alldone
    
selstmt1x:
FETCH curs INTO :varaa,:varbb,:varcc
.
.
GOTO selstmt1x
    
alldone:
CLOSE curs

Related Information