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.