sql - 存储过程中的多个表需要通过vba在excel中生成

Question

我只处理过返回单个表格，所以我不知道该怎么做。我无权访问存储过程。我知道这完全取决于初始 ID，而初始 ID 又可以连接到此人拥有的帐户列表。
它返回 4 个表，但是当我尝试返回数据时，我只得到初始表。是否有一些我不知道该怎么做的迭代？我知道没有 sp 的代码很难，但到目前为止，这是我对 vba 的看法:

 Sub special_customer_data()
    
        Dim con As ADODB.Connection
        Dim cmd As ADODB.Command
        Dim rs As ADODB.Recordset
    
        
        Set con = New ADODB.Connection
        Set cmd = New ADODB.Command
        Set rs = New ADODB.Recordset
        
        con.Open "database connection"
        
        cmd.ActiveConnection = con
        
        cmd.CommandType = adCmdStoredProc
        cmd.CommandText = "sp"
        
        cmd.Parameters("@cust_id").Value = data_feed.Range("B1").Value
        
        Set rs = cmd.Execute
        write_results rs
        
        rs.Close
        con.Close
    
    End Sub

Sub write_results(ResultSet As ADODB.Recordset)

Sheets("data_feed").Range("A6:AF1000").ClearContents

Sheets("data_feed").Range("A6").CopyFromRecordset ResultSet
Sheets("data_feed").Range("A6").CurrentRegion.EntireColumn.AutoFit
    
End Sub

Answer 1

正如我在评论中所说:
https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/nextrecordset-method-example-vb?view=sql-server-ver15
https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/nextrecordset-method-ado?view=sql-server-ver15

Use the NextRecordset method to return the results of the next command in a compound command statement or of a stored procedure that returns multiple results. If you open a Recordset object based on a compound command statement (for example, "SELECT * FROM table1;SELECT * FROM table2") using the Execute method on a Command or the Open method on a Recordset, ADO executes only the first command and returns the results to recordset. To access the results of subsequent commands in the statement, call the NextRecordset method.

这意味着您需要调用 rs.NextRecordset和 write_results rs直到你有所有的数据。