从Excel和VBA中选择和更新存储过程。

I have created a stored procedure that involves a simple UPDATE and then SELECT statement, which works fine in SSMS - the update works and the select produces a recordset.

我创建了一个存储过程，其中包含一个简单的更新，然后是SELECT语句，它在SSMS中工作得很好——UPDATE工作，SELECT生成一个记录集。

The problem is when I am trying:

问题是当我尝试的时候:

Set rs = New ADODB.Recordset             'works fine
conn.Open sConnString                    'works fine
Set rs = conn.Execute("EXEC uspUpdateManual")

I get a 'operation is not allowed when the object is closed 3704' error. If I comment out the update part of the stored procedure, the select works fine on its own and the recordset is dropped into Excel.

当对象关闭3704时，不允许进行操作。如果我注释掉存储过程的更新部分，那么select本身就可以正常工作，记录集就会被删除到Excel中。

SP:

SP:

ALTER PROCEDURE [dbo].[uspUpdateManual] 
AS
BEGIN TRANSACTION
    UPDATE Table1
    SET ACC = '9'
COMMIT TRANSACTION
BEGIN TRANSACTION
    SELECT * FROM Table1
COMMIT TRANSACTION
END
GO

Is there any way to do both update and select in one, or do I have to separate these into two stored procedures?

有什么方法可以同时在一个过程中进行更新和选择吗?或者我必须将它们分开到两个存储过程中吗?

1 个解决方案

#1

I think the problem possibly here is with transactions, could you show the procedure code please?. You could try and wrap the update in a transaction and the select in another as show below:

我想这里的问题可能与事务有关，你能给我看一下程序代码吗?您可以尝试在事务中包装更新，然后在另一个事务中包装选择，如下所示:

BEGIN TRANSACTION

UPDATE...

COMMIT TRANSACTION

BEGIN TRANSACTION

SELECT...

COMMIT TRANSACTION