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.