16.20 - Details About Dynamic Result Sets - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1184-162K
Language
English (United States)

You are not limited to the result of an OUT or INOUT value for a CALL request. Dynamic result sets permit an SQL procedure to return a single or as many as 15 result sets. Each result set is returned as it would be for a single multistatement request, not unlike a macro that contains several SELECT requests. The response is the same as the output from a macro: if there are multiple SELECT statements in a macro, then it produces multiple statement responses to the SELECT requests.

The process by which procedures do this is as follows.

  1. Using its DYNAMIC RESULT SETS number_of_sets specification, the CREATE PROCEDURE statement establishes the maximum number of result sets the procedure can return. This value for number_of_sets can range from 0 to 15, inclusive. The specified range applies only to dynamic result sets and does not include results returned in INOUT or OUT parameters.

    The system returns the result sets in the form a multistatement response spool to the client application.

  2. A DECLARE CURSOR statement enables the procedure to create a result set. The SELECT clause of the DECLARE CURSOR statement determines the result set. The DECLARE CURSOR statement must also contain a WITH RETURN clause to be a result set cursor. See SQL Stored Procedures and Embedded SQL for details.
  3. The system creates the result set when it opens the cursor with an OPEN statement.

    The OPEN statement causes the SELECT clause in the DECLARE CURSOR statement to be executed, producing the result set. The result sets are returned in the order they were opened.

    The cursor must remain open when the procedure exits in order for the result set to be returned to the caller. This contrasts the case where a procedure returns a single result set, where all cursors are closed when the procedure exits. Cursors are also closed when exiting a compound statement if the cursor was opened within the compound statement.

    However, a cursor that specifies a WITH RETURN clause is not closed when the procedure exits. If the procedure does close the result cursor, then the result set is deleted and not returned to the caller.

    Result sets are returned in the order they were opened.

  4. If a procedure is called from a client or an external procedure, the result set is returned in the form of a multistatement result. This is exactly the same form that would be created if a macro that contained multiple SELECT statements within it was executed.

    The first result is for the first statement, and are the results for any OUT or INOUT arguments to the procedure. The second and subsequent results spool are the output for the result sets in the order they were opened.

    Procedure Called By Result Set Returned To
    Procedure. Procedure.
    Client application. Client application.
  5. A procedure can use the dynamic form of the DECLARE CURSOR statement.

    In this case, the DECLARE CURSOR statement specifies a statement name in place of the SELECT statement that it would specify for the static case.

    The statement name is referenced by the PREPARE statement to prepare the dynamic SELECT statement contained in a string. The prepared SELECT statement is executed when the cursor is opened with the OPEN statement.

The system supports this functionality by means of the following SQL cursor and control language statements.
  • CREATE PROCEDURE and REPLACE PROCEDURE statement DYNAMIC RESULT SETS clause.

    The DYNAMIC RESULT SETS clause specifies the maximum number of result sets to be returned.

  • DECLARE CURSOR statement WITH RETURN clause.

    The WITH RETURN clause specifies that the cursor to be opened is a result set cursor to be returned to the client or to the caller.

    See Rules and Limitations for Dynamic Result Sets and Teradata Vantage™ SQL Stored Procedures and Embedded SQL , B035-1148 for details.

  • PREPARE statement

    This statement enable a procedure to create a dynamic DECLARE CURSOR statement to allow the creation of different result sets.

    PREPARE allows dynamic parameter markers.

    See Teradata Vantage™ SQL Stored Procedures and Embedded SQL , B035-1148 for details.

  • OPEN statement USING clause

    The USING clause supports the use of dynamic parameter markers used in a dynamic DECLARE CURSOR statement.

    See Teradata Vantage™ SQL Stored Procedures and Embedded SQL , B035-1148 for details.

A DYNAMIC RESULT SETS clause must be specified to return a result set to the caller of the procedure, whether the caller is a client application or another procedure. This clause is optional and is not used if the procedure does not return a result set. If you do not specify a DYNAMIC RESULT SETS clause, then the system assumes zero result sets. Note that this total does not include results returned in an INOUT or OUT parameter.

You can return a result set to either a calling procedure or to a client application, but not to both.

The result set output for a procedure appears in the response spool after the results for the procedure OUT or INOUT parameters result row. The result set is a response spool similar to how a response spool would appear if a macro had been written to perform several SELECT statements. The only difference is an extra parcel indicating that the result set came from a procedure.

For example, the response spool for the following macro and the procedure following it are identical except for the title for the procedure output arguments versus the macro and the success response.

     CREATE MACRO sample_m (c INTEGER) AS (
       SELECT :c+1;

       SELECT *
       FROM m1;

       SELECT *
       FROM m2
       WHERE m2.a > :c;) ;

The BTEQ output from this macro is as follows.

     EXEC sample_m(1);
      *** Query completed. One row found. One column returned.
      *** Total elapsed time was 1 second.

           (c+1)
     -----------
               2

      *** Query completed. One row found. 2 columns returned.

               a                       b
     -----------  ----------------------
               1   2.00000000000000E 000

      *** Query completed. One row found. 2 columns returned.

               a                       b
     -----------  ----------------------
               2   4.00000000000000E 000

The following procedure that uses result sets produces the same output.

     CREATE PROCEDURE sample_p (INOUT c INTEGER)
       DYNAMIC RESULT SETS 2
         BEGIN
            DECLARE cursor_1 CURSOR WITH RETURN FOR
              SELECT * FROM m1;
            DECLARE cursor_2 CURSOR WITH RETURN FOR
              SELECT * FROM m2 WHERE m2.a > c;
            SET c = c +1;
            OPEN cursor_1;
            OPEN cursor_2;
         END;

The BTEQ output from the procedure is as follows.

     CALL sample_p(1);
      *** Procedure has been executed.
      *** Total elapsed time was 1 second.

               1
     -----------
               2

      *** Procedure has been executed. One row found. 2 columns returned.
      *** Starting Row Number: 1
      *** Database Name: FSK
      *** Procedure Name: SAMPLE_P

               a                       b
     -----------  ----------------------
               1   2.00000000000000E 000

      *** Procedure has been executed. One row found. 2 columns returned.
      *** Starting Row Number: 1
      *** Database Name: FSK
      *** Procedure Name: SAMPLE_P

               a                       b
     -----------  ----------------------
               2   4.00000000000000E 000

The called procedure that contains the DYNAMIC result sets clause returns the following possible SQLSTATE warnings.

SQLSTATE Code Meaning
‘0100C’ The procedure returned additional result sets.

The system returns this SQLSTATE warning code when the procedure produces result sets.

‘0100E’ The procedure attempted to return too many result sets.

The system returns this SQLSTATE warning code when the procedure leaves more result set cursors open than are specified in the DYNAMIC RESULT SETS clause.