存储过程执行后，记录集关闭 [英] Recordset Closed After Stored Procedure Execution

问题描述

我正在VBA中使用ADO执行存储过程。我正在尝试使用SQL Server 2008中存储过程的结果填充记录集。下面的VBA示例：

I'm executing a stored procedure using ADO in VBA. I'm trying to populate the recordset with the results from a stored procedure in SQL Server 2008. Example of the VBA below:

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 ... 行，这向我表明已存储过程不会返回结果。

I am receiving the error Operation is not allowed when the object is closed on the line If Not oRS.BOF... which indicates to me that the stored procedure is not returning a result.

但是，如果我在SSMS中执行存储过程，它将返回一行。 SP遵循以下原则：

However if I execute the stored procedure in SSMS, it returns a single row. The SP goes along the lines of:

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中执行该过程的结果是：

The result of executing the procedure in SSMS is:

id    col1
__    ____
1     1

有人可以帮助关闭记录集/为什么不填充记录集吗？

Could anyone help with why the recordset is being closed / not filled?

推荐答案

基于类似的问题：在执行存储过程时关闭对象时不允许操作 我在注释中建议：

Based on similar question: "Operation is not allowed when the object is closed" when executing stored procedure i recommended in comment:

我怀疑2个原因：1）您的sp不包含： SET NOCOUNT ON; 和2）您正在处理类型为table的变量。

I suspect 2 reasons: 1) your sp does not contains: SET NOCOUNT ON; and 2) you're working on variable of type: table.

关闭对象时不允许操作的最常见原因这是因为该存储过程不包含 SET NOCOUNT ON 命令，这可以防止多余的结果集干扰 SELECT 语句。

The most common reason of Operation is not allowed when the object is closed is that that stored procedure does not contain SET NOCOUNT ON command, which prevent extra result sets from interfering with SELECT statements.

有关更多信息，请参见：设置NOCOUNT（Transact-SQL）

For further information, please see: SET NOCOUNT (Transact-SQL)

这篇关于存储过程执行后，记录集关闭的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！