当我们在JDBC中执行存储过程时，我们会得到一系列零个或多个结果。然后我们可以通过调用 CallableStatement＃getMoreResults（）来顺序处理这些结果。每个结果都可以包含

When we execute a stored procedure in JDBC we get back a series of zero or more "results". We can then process those "results" sequentially by calling CallableStatement#getMoreResults(). Each "result" can contain

• 我们可以使用 ResultSet $检索零行或多行数据c $ c> object，


• 我们可以使用 CallableStatement＃getUpdateCount（）$检索的DML语句（INSERT，UPDATE，DELETE）的更新计数c $ c>，或


• 抛出SQLServerException的错误。

• zero or more rows of data that we can retrieve with a ResultSet object,
• an update count for a DML statement (INSERT, UPDATE, DELETE) that we can retrieve with CallableStatement#getUpdateCount(), or
• an error that throws an SQLServerException.

对于问题 1问题通常是存储过程不以 SET NOCOUNT ON; 开头，并在执行SELECT生成结果集之前执行DML语句。 DML的更新计数作为第一个结果返回，数据行卡在它后面，直到我们调用 getMoreResults 。

For "Issue 1" the problem is often that the stored procedure does not begin with SET NOCOUNT ON; and executes a DML statement before doing a SELECT to produce a result set. The update count for the DML is returned as the first "result", and the data rows are "stuck behind it" until we call getMoreResults.

问题2基本上是同一个问题。存储过程在错误发生之前产生结果（通常是SELECT，或可能是更新计数）。错误在随后的结果中返回，并且在我们使用 getMoreResults 检索它之前不会导致异常。

"Issue 2" is essentially same problem. The stored procedure produces a "result" (usually a SELECT, or possibly an update count) before the error occurs. The error is returned in a subsequent "result" and does not cause an exception until we "retrieve" it using getMoreResults.

在许多情况下，只需添加 SET NOCOUNT ON; 作为存储过程中的第一个可执行语句即可避免此问题。但是，对存储过程的更改并不总是可行的，事实仍然是，为了从存储过程中获取所有，我们需要继续调用 getMoreResults 直到，正如Javadoc所说：

In many cases the problem can be avoided by simply adding SET NOCOUNT ON; as the first executable statement in the stored procedure. However, a change to the stored procedure is not always possible and the fact remains that in order to get everything back from the stored procedure we need to keep calling getMoreResults until, as the Javadoc says:

There are no more results when the following is true: 

     // stmt is a Statement object
     ((stmt.getMoreResults() == false) && (stmt.getUpdateCount() == -1))

听起来很简单，但像往常一样，魔鬼在细节中，如下例所示。对于SQL Server存储过程...

That sounds simple enough but as usual, "the devil is in the details", as illustrated by the following example. For a SQL Server stored procedure ...

ALTER PROCEDURE dbo.TroublesomeSP AS
BEGIN
    -- note: no `SET NOCOUNT ON;`
    DECLARE @tbl TABLE (id VARCHAR(3) PRIMARY KEY);

    DROP TABLE NonExistent;
    INSERT INTO @tbl (id) VALUES ('001');
    SELECT id FROM @tbl;
    INSERT INTO @tbl (id) VALUES ('001');  -- duplicate key error
    SELECT 1/0;  -- error _inside_ ResultSet
    INSERT INTO @tbl (id) VALUES ('101');
    INSERT INTO @tbl (id) VALUES ('201'),('202');
    SELECT id FROM @tbl;
END

...以下Java代码将返回所有内容...

... the following Java code will return everything ...

try (CallableStatement cs = conn.prepareCall("{call dbo.TroublesomeSP}")) {
    boolean resultSetAvailable = false;
    int numberOfResultsProcessed = 0;
    try {
        resultSetAvailable = cs.execute();
    } catch (SQLServerException sse) {
        System.out.printf("Exception thrown on execute: %s%n%n", sse.getMessage());
        numberOfResultsProcessed++;
    }
    int updateCount = -2;  // initialize to impossible(?) value
    while (true) {
        boolean exceptionOccurred = true; 
        do {
            try {
                if (numberOfResultsProcessed > 0) {
                    resultSetAvailable = cs.getMoreResults();
                }
                exceptionOccurred = false;
                updateCount = cs.getUpdateCount();
            } catch (SQLServerException sse) {
                System.out.printf("Current result is an exception: %s%n%n", sse.getMessage());
            }
            numberOfResultsProcessed++;
        } while (exceptionOccurred);

        if ((!resultSetAvailable) && (updateCount == -1)) {
            break;  // we're done
        }

        if (resultSetAvailable) {
            System.out.println("Current result is a ResultSet:");
            try (ResultSet rs = cs.getResultSet()) {
                try {
                    while (rs.next()) {
                        System.out.println(rs.getString(1));
                    }
                } catch (SQLServerException sse) {
                    System.out.printf("Exception while processing ResultSet: %s%n", sse.getMessage());
                }
            }
        } else {
            System.out.printf("Current result is an update count: %d %s affected%n",
                    updateCount,
                    updateCount == 1 ? "row was" : "rows were");
        }
        System.out.println();
    }
    System.out.println("[end of results]");
}

...产生以下控制台输出：

... producing the following console output:

Exception thrown on execute: Cannot drop the table 'NonExistent', because it does not exist or you do not have permission.

Current result is an update count: 1 row was affected

Current result is a ResultSet:
001

Current result is an exception: Violation of PRIMARY KEY constraint 'PK__#314D4EA__3213E83F3335971A'. Cannot insert duplicate key in object 'dbo.@tbl'. The duplicate key value is (001).

Current result is a ResultSet:
Exception while processing ResultSet: Divide by zero error encountered.

Current result is an update count: 1 row was affected

Current result is an update count: 2 rows were affected

Current result is a ResultSet:
001
101
201
202

[end of results]