| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic |
|---|---|---|---|---|---|---|---|
Using multiple result sets |
Learn how to query and retrieve multiple result sets from a single execute using the JDBC Driver for SQL Server. |
David-Engel |
v-davidengel |
08/12/2019 |
sql |
connectivity |
conceptual |
[!INCLUDEDriver_JDBC_Download]
When working with inline SQL or [!INCLUDEssNoVersion] stored procedures that return more than one result set, the [!INCLUDEjdbcNoVersion] 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 [!INCLUDEssSampleDBnormal] sample database is passed in to the function, and an SQL statement is constructed that, when run, returns two result sets:
[!codeJDBC#UsingMultipleResultSets1]
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.