使用ADO调用INSERT存储过程无法返回结果集，如何验证执行状态？

我有一个INSERT存储过程，在SQL Server中执行时完全正常，但使用ADO调用该存储过程时，无法按预期返回结果集，尝试获取受影响行数也得到同样的问题。我的核心目标是确保该存储过程无错误执行，以便在应用程序中据此做出决策。请问最佳的解决方法是什么？

存储过程代码 #

CREATE PROCEDURE MyProc
AS
    DECLARE @sql AS NVARCHAR(MAX)
    SET NOCOUNT ON;
BEGIN
    SELECT @sql = 'INSERT INTO tableB (col1, col2) 
                       SELECT c1, c2 
                       FROM TableA 
                       LEFT JOIN tableB ON tableA.Id = tableB.Id;'
END

BEGIN
    EXECUTE(@sql)        
    SELECT @@ERROR AS ErrorNumber
    RETURN 
END

ADO/VBA调用代码 #

DIM rs AS ADODB.Recordset

rs.Open "exec MyProc", MyActiveConnection

1. 修复存储过程的核心问题 #

你的存储过程存在逻辑冗余、错误捕获不完善的问题，导致ADO无法正确获取执行状态。修改后的存储过程如下：

CREATE PROCEDURE MyProc
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @sql AS NVARCHAR(MAX);
    DECLARE @ErrorNumber INT;

    -- 构造动态SQL，添加过滤条件避免重复插入
    SET @sql = 'INSERT INTO tableB (col1, col2) 
                SELECT c1, c2 
                FROM TableA 
                LEFT JOIN tableB ON tableA.Id = tableB.Id
                WHERE tableB.Id IS NULL;';

    BEGIN TRY
        -- 用sp_executesql替代EXEC，更安全且支持参数化
        EXECUTE sp_executesql @sql;
        -- 执行成功时返回错误码0和受影响行数
        SELECT 0 AS ErrorNumber, @@ROWCOUNT AS AffectedRows;
    END TRY
    BEGIN CATCH
        -- 捕获错误时返回实际错误码和0行受影响
        SELECT ERROR_NUMBER() AS ErrorNumber, 0 AS AffectedRows;
        -- 若需要让ADO直接捕获错误，可添加THROW;语句
    END CATCH
END

2. 调整ADO/VBA调用逻辑 #

原调用只读取第一个结果集，但存储过程可能存在隐含的空结果集，必须遍历所有结果集才能拿到最终状态：

DIM rs AS ADODB.Recordset
DIM errorNum AS Integer
DIM affectedRows AS Long

Set rs = New ADODB.Recordset
rs.Open "exec MyProc", MyActiveConnection, adOpenForwardOnly, adLockReadOnly

-- 遍历所有结果集，获取最后一个有效结果
Do Until rs Is Nothing
    If Not rs.EOF Then
        errorNum = rs("ErrorNumber").Value
        affectedRows = rs("AffectedRows").Value
    End If
    Set rs = rs.NextRecordset
Loop

-- 根据错误码判断执行状态
If errorNum = 0 Then
    MsgBox "执行成功，受影响行数：" & affectedRows
Else
    MsgBox "执行失败，错误码：" & errorNum
End If

rs.Close
Set rs = Nothing

关键说明 #

• 使用TRY/CATCH块比@@ERROR更全面，能捕获执行过程中所有类型的错误。
• sp_executesql比直接EXEC更安全，可预防SQL注入，同时支持后续扩展参数化查询。
• 原动态SQL未过滤已存在数据，会导致重复插入，添加WHERE tableB.Id IS NULL可避免该问题。
• ADO必须遍历所有结果集，因为存储过程中SET NOCOUNT ON之前的操作可能生成空结果集，只有最后一个结果集才包含我们需要的执行状态。

内容的提问来源于stack exchange，提问作者Ibo