17.05 - Example: Calling a Stored Procedure that Returns Dynamic Result Sets from Embedded SQL - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

Consider the following stored procedure that returns a limit of four dynamic result sets to a calling embedded SQL application:

     CREATE PROCEDURE  TESTSP1(INOUT p1 INTEGER, OUT p2 INTEGER)
     DYNAMIC RESULT SETS 4
         BEGIN
          DECLARE tab1_cursor CURSOR WITH RETURN ONLY TO CALLER FOR
                              SELECT c1, c2, c3 FROM tab1;
                              OPEN tab1_cursor;
                              SET p1 = p1 + 1;
                              SET p2 = 82;
         END;

To access the dynamic result set rows returned to the application by this procedure, declare a cursor like the following:

     EXEC SQL BEGIN DECLARE SECTION;
          …
        long H1;
        long H2;

        long M1;
        long M2;
        long M3;
          …
     EXEC SQL END DECLARE SECTION;
          …
     EXEC SQL
            DECLARE TESTCUR CURSOR
                FOR 'CALL TESTSP1(:H1, :H2)';
     EXEC SQL
            OPEN TESTCUR;

If the procedure returns one or more rows, the system sets SQLCODE to 3212 and SQLSTATE to ‘T3212’. This example does not use SQLSTATE return codes. The SQLSTATE codes equivalent to the SQLCODE values in the example are provided for the sake of completeness.

If the stored procedure returns no rows, the system sets SQLCODE to 0 and SQLSTATE to ‘T0000’:

     if (SQLCODE == 3212)
           fetch_rows();
     else if (SQLCODE != 0)
           error_check();

Fetching rows from a cursor declared for a stored procedure is similar to fetching rows from a cursor defined for an SQL request:

     void fetch_rows()
              do  {
                          EXEC SQL
                               FETCH TESTCUR INTO :M1, :M2, :M3;
                          …
                  } while (SQLCODE == 0);