存储过程执行后记录集已关闭。

Question

我在使用VBA中的ADO执行存储过程。 我试图将来自SQL Server 2008存储过程的结果填充到记录集中。以下是VBA示例：

Public Function DoSomething() As Variant()

Dim oDB As ADODB.Connection: Set oDB = New ADODB.Connection
Dim oCM As ADODB.Command: Set oCM = New ADODB.Command
Dim oRS As ADODB.Recordset

oDB.Open gcConn

With oCM
    .ActiveConnection = oDB
    .CommandType = adCmdStoredProc
    .CommandText = "spTestSomething"
    .NamedParameters = True
    .Parameters.Append .CreateParameter("@Param1", adInteger, adParamInput, , 1)
    Set oRS = .Execute
End With

If Not oRS.BOF And Not oRS.EOF Then 'Error thrown here'
    DoSomething = oRS.GetRows()
Else
    Erase DoSomething
End If

oRS.Close
Set oRS = Nothing
oDB.Close
Set oDB = Nothing

End Function

我收到了错误信息对象关闭时不允许操作，该错误出现在代码行If Not oRS.BOF...。这告诉我存储过程没有返回结果。

然而，如果我在SSMS中执行存储过程，它会返回一行结果。该存储过程大致如下：

CREATE PROC spTestSomething
    @Param1 int
AS
BEGIN

    DECLARE @TempStore table(id int, col1 int);

    INSERT INTO table1
        (param1)
        OUTPUT inserted.id, inserted.col1
        INTO @TempStore
    VALUES
        (@Param1);

    EXEC spOtherSP;

    SELECT
        id,
        col1
    FROM
        @TempStore;
END
GO

在SSMS中执行该过程的结果如下：

id    col1
__    ____
1     1

有人可以帮忙解释一下为什么记录集被关闭或未填充吗？

- Gareth

---

要识别SP影响了多少记录，请使用Execute方法与参数一起使用：https://msdn.microsoft.com/en-us/library/windows/desktop/ms681559%28v=vs.85%29.aspx，而不是BOF和EOF。 - Maciej Los

@MaciejLos 感谢您的回复。由于有多个存储过程正在执行，因此会影响多行。您是否建议在尝试使用记录集之前先检查这一点？谢谢。 - Gareth

是的。在这种情况下，不需要检查BOF和EOF方法返回的值。如果RecordsAffected参数返回大于零的值，则记录集已填充有值;) - Maciej Los

@MaciejLos 大多数情况下，受影响的记录在 MS Access 中将返回 -1。 - Fionnuala

使用 SET NOCOUNT ON 是完全正确的，谢谢。把它放在回答中，我会接受的。再次感谢。 - Gareth

Answer 1

根据类似问题:“Operation is not allowed when the object is closed” when executing stored procedure 我在评论中推荐以下两个原因：1）您的存储过程不包含：SET NOCOUNT ON;，2）您正在处理类型为表格的变量。 “操作无法在对象关闭时执行”最常见的原因是存储过程没有包含SET NOCOUNT ON命令，这可以防止额外的结果集干扰SELECT语句。

有关更多信息，请参见：SET NOCOUNT (Transact-SQL)。

- Maciej Los