Using multiple result sets
When working with inline SQL or SQL Server stored procedures that return more than one result set, the Microsoft JDBC Driver for SQL Server provides the getResultSet method in the SQLServerStatement class for retrieving each set of data returned. In addition, when running a statement that returns more than one result set, you can use the execute method of the SQLServerStatement class, because it will return a boolean value that indicates if the value returned is a result set or an update count.
If the execute method returns true, the statement that was run has returned one or more result sets. You can access the first result set by calling the getResultSet method. To determine if more result sets are available, you can call the getMoreResults method, which returns a boolean value of true if more result sets are available. If more result sets are available, you can call the getResultSet method again to access them, continuing the process until all result sets have been processed. If the getMoreResults method returns false, there are no more result sets to process.
If the execute method returns false, the statement that was run has returned an update count value, which you can retrieve by calling the getUpdateCount method.
Note
For more information about update counts, see Using a stored procedure with an update count.
In the following example, an open connection to the AdventureWorks2022 sample database is passed in to the function, and a SQL statement is constructed that, when run, returns two result sets:
public static void executeStatement(Connection con) {
try (Statement stmt = con.createStatement();) {
String SQL = "SELECT TOP 10 * FROM Person.Contact; SELECT TOP 20 * FROM Person.Contact";
boolean results = stmt.execute(SQL);
int rsCount = 0;
// Loop through the available result sets.
do {
if (results) {
ResultSet rs = stmt.getResultSet();
rsCount++;
// Show data from the result set.
System.out.println("RESULT SET #" + rsCount);
while (rs.next()) {
System.out.println(rs.getString("LastName") + ", " + rs.getString("FirstName"));
}
}
System.out.println();
results = stmt.getMoreResults();
} while (results);
}
// Handle any errors that may have occurred.
catch (SQLException e) {
e.printStackTrace();
}
}
In this case, the number of result sets returned is known to be two. However, the code is written so that if an unknown number of result sets were returned, such as when calling a stored procedure, they would all be processed. To see an example of calling a stored procedure that returns multiple result sets along with update values, see Handling complex statements.
Note
When you make the call to the getMoreResults method of the SQLServerStatement class, the previously returned result set is implicitly closed.