Dynamic Result Sets | CREATE PROCEDURE (SQL Form) | Teradata Vantage - Details About Dynamic Result Sets - Advanced SQL Engine - Teradata Database

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 Teradata Vantage™ - SQL Stored Procedures and Embedded SQL, B035-1148 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.

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.