Consuming Result Sets Created by Calling a Stored Procedure | Vantage - Consuming Result Sets Created by Calling a Stored Procedure - Analytics Database - Teradata Vantage

An external stored procedure can call a stored procedure that creates up to 15 dynamic result sets.

Here is a code example for a Java external stored procedure that calls a stored procedure called SQL_SP_W_RS and consumes result sets created by the stored procedure:

/**************************************************************
   DDL for the Java external stored procedure:

   REPLACE PROCEDURE CountRegions(  OUT  RowsFound INTEGER )
     LANGUAGE JAVA
     READS SQL DATA
     PARAMETER STYLE JAVA
     EXTERNAL NAME 'JarXSP:region.countRegion';
 **************************************************************/

public class region {

  public static void countRegion(int[] rowcount) throws SQLException
  {
    /* Establish default connection. */
    Connection con =
       DriverManager.getConnection("jdbc:default:connection");

    /* Call SQL stored procedure that returns a result set */
    CallableStatement stmt =
       con.prepareCall("CALL SQL_SP_W_RS( ? )");
    stmt.setInt(1,1);

    /* Check that a result set was returned. */
    if (stmt.execute()) {
       /* Get returned result set. */
       ResultSet rs = stmt.getResultSet();
       rowcount[0]=0;
       /* Count rows in result set. */
       while (rs.next()) {
          rowcount[0]++;
       }
    }
  }

  ...

}

For details on how to return result sets from a stored procedure, see Teradata Vantage™ - SQL Stored Procedures and Embedded SQL, B035-1148.