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

本文介绍了存储过程执行后，记录集关闭的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在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)