SQL Server

How to Use the ADO NextRecordset Method

The ADO .NextRecordset method allows you to handle multiple result sets from a single SQL Server stored procedure.

Mike Wolfe

May 3, 2024 • 6 min read

SQL Server stored procedures allow you to return multiple recordsets.

This is a handy way to reduce clutter by grouping related SELECT statements into a single SPROC. I never thought to take advantage of this fact because before today I did not know about ADO's .NextRecordset method. Using that method, we can process the results of each SELECT statement independently of the others.

Let's explore a practical example.

What We Will Learn

In addition to the .NextRecordset method, you will learn several other interesting things from the sample code and T-SQL below:

Using Common Table Expressions in T-SQL
Using the undocumented spt_values table from the SQL Server master database
Using several T-SQL date functions, including DATEFROMPARTS, DATENAME, EOMONTH, DATEDIFF, DATEADD, and DATEPART
Using ADO to execute a stored procedure from VBA
Passing a parameter to a stored procedure
Using the CopyFromRecordset method of the Excel Range object

Populating Multiple Excel Tabs With SPROC Output

Here's what we are going to do:

Create a SPROC that runs three SELECT queries
Call the SPROC from Excel
Create and populate a new Excel tab with each SELECT query

Sample Stored Procedure

To focus on the purpose of this article–using the .NextRecordset method–I asked ChatGPT to create a SPROC that returned some mundane, but simple to understand, data:

[MIKE]: Write a stored procedure in t-sql that takes a year as the only argument and returns three record sets:

1. One row for each month of the provided year with the following columns :

month name
start date
End date
number of days

2. One row for each week of the provided year with the following columns:

week number
start date
End date

3. One row for each day of the provided year with the following columns:

date
Day of the week
Day number for the year

[GPT-4]: To create a stored procedure in T-SQL that returns the specified record sets, you can use the following script. This procedure utilizes common table expressions (CTEs) to generate the required date ranges and details for each record set:

CREATE PROCEDURE GetYearlyCalendarInformation
    @Year INT
AS
BEGIN
    -- Define the starting and ending dates of the year
    DECLARE @StartDate DATE = DATEFROMPARTS(@Year, 1, 1);
    DECLARE @EndDate DATE = DATEFROMPARTS(@Year, 12, 31);

    -- First Record Set: Month details
    ;WITH Months AS (
        SELECT
            DATENAME(month, DATEADD(month, v.number, @StartDate)) AS MonthName,
            DATEFROMPARTS(@Year, v.number + 1, 1) AS StartDate,
            EOMONTH(DATEFROMPARTS(@Year, v.number + 1, 1)) AS EndDate
        FROM master.dbo.spt_values v
        WHERE v.type = 'P' AND v.number BETWEEN 0 AND 11
    )
    SELECT
        MonthName,
        StartDate,
        EndDate,
        DATEDIFF(day, StartDate, DATEADD(day, 1, EndDate)) AS NumberOfDays
    FROM Months;

    -- Second Record Set: Week details
    ;WITH Weeks AS (
        SELECT
            DATEPART(week, DATEADD(day, idx.number, @StartDate)) AS WeekNumber,
            MIN(DATEADD(day, idx.number, @StartDate)) AS StartDate,
            MAX(DATEADD(day, idx.number, @StartDate)) AS EndDate
        FROM master.dbo.spt_values idx
        WHERE idx.type = 'P' AND DATEADD(day, idx.number, @StartDate) <= @EndDate
        GROUP BY DATEPART(week, DATEADD(day, idx.number, @StartDate))
    )
    SELECT 
        WeekNumber,
        StartDate,
        EndDate
    FROM Weeks;

    -- Third Record Set: Day details
    SELECT
        DATEADD(day, idx.number, @StartDate) AS [Date],
        DATENAME(weekday, DATEADD(day, idx.number, @StartDate)) AS [DayOfWeek],
        DATEPART(dayofyear, DATEADD(day, idx.number, @StartDate)) AS [DayNumber]
    FROM master.dbo.spt_values idx
    WHERE idx.type = 'P' AND DATEADD(day, idx.number, @StartDate) <= @EndDate;
END;
GO

Explanation

Months CTE: Generates months from 1 to 12 for the given year. It calculates the first day of each month and the last day using the EOMONTH function.
Weeks CTE: Calculates the week number using DATEPART(week, ...). It groups by week number after adjusting each date within the year. The minimum and maximum dates within each group determine the start and end dates of each week.
Days: Directly calculates each day within the year, returning the date, day of the week, and the day number within the year.

Usage

To use this stored procedure, you would call it with a specific year like so:

EXEC GetYearlyCalendarInformation @Year = 2024;

This stored procedure assumes that you have access to the system table master.dbo.spt_values, which is commonly used for generating sequences of numbers in SQL Server. Make sure that the SQL Server account running this procedure has the appropriate permissions. Adjustments might be necessary if spt_values is not available or for specific SQL Server configurations.

Sample Excel VBA Code

Next, I asked ChatGPT to create some specific VBA code to demonstrate the .NextRecordset method.

Some important notes about this prompt and the generated code:

I explicitly tell ChatGPT to use the .NextRecordset method in its solution
I only show the original prompt, but the final code is the result of several iterations of refinements and some final tweaking to the code

I point this out because ChatGPT and other LLMs will not do your thinking for you. When it comes to writing code, ChatGPT is more like an intern than a mentor:

It can save you a lot of time, but you are ultimately responsible for reviewing and testing the results. Also, keep in mind, reading code is twice as hard as writing code (for humans, at least). That means you have to be an excellent programmer to use AI-generated code responsibly. If you are still learning, you are much better off writing your own code and asking AI to review it and suggest improvements.

Without further ado, here is the code:

[MIKE]: Create VBA for Excel that uses ADO, the .NextRecordset method, and the Excel Range's CopyFromRecordset method to populate three Excel worksheets with the record sets from the earlier sproc

To use VBA in Excel to execute the stored procedure created in T-SQL and populate three different worksheets with the results using ADO (ActiveX Data Objects), follow these steps. This example assumes that you already have a SQL Server database connection and that the stored procedure GetYearlyCalendarInformation is properly set up and available.

Step 1: Set Up the VBA Environment

Open Excel.
Press ALT + F11 to open the VBA editor.
In the VBA editor, go to Tools -> References and check Microsoft ActiveX Data Objects x.x Library where x.x is the highest version number available.

Step 2: Write the VBA Code

Insert a new module and copy the following VBA code into the module. This code defines a subroutine that connects to the SQL Server, executes the stored procedure, and uses .CopyFromRecordset to populate worksheets.

Sub PopulateWorksheetsWithRecordsets()
    ' Initialize the connection
    Dim Conn As ADODB.Connection
    Set Conn = New ADODB.Connection
    Conn.ConnectionString = "Provider=SQLOLEDB;Data Source=MyServer; " & _
                            "Initial Catalog=MyDatabase; Integrated Security=SSPI;"
    Conn.Open
    
    ' Initialize the command
    Dim Cmd As ADODB.Command
    Set Cmd = New ADODB.Command
    With Cmd
        .ActiveConnection = Conn
        .CommandText = "GetYearlyCalendarInformation"
        .CommandType = adCmdStoredProc
        Dim YearParam As Integer
        YearParam = 2024 ' Specify the required year
        .Parameters.Append .CreateParameter("@Year", adInteger, adParamInput, , YearParam)
    End With
    
    ' Execute the command
    Dim rs As ADODB.Recordset
    Set rs = Cmd.Execute
    
    ' Worksheet names for each recordset
    Dim wsNames As Variant
    wsNames = Array("Months", "Weeks", "Days")
    
    ' Loop through each recordset and copy to a new worksheet
    Dim wsIndex As Integer
    wsIndex = 0
    Do Until rs Is Nothing
        ' Create a new worksheet for each recordset
        Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = wsNames(wsIndex)
        Worksheets(wsNames(wsIndex)).Range("A1").CopyFromRecordset rs
        wsIndex = wsIndex + 1
        Set rs = rs.NextRecordset
    Loop
    
    ' Clean up
    Conn.Close
    Set rs = Nothing
    Set Cmd = Nothing
    Set Conn = Nothing
    
    MsgBox "Data has been successfully imported into worksheets!"
End Sub

Step 3: Execute the VBA Code

Ensure that the SQL Server details (server name and database name) are correctly entered in the VBA subroutine.
In the VBA editor, press F5 to run the PopulateWorksheetsWithRecordsets subroutine.

Explanation:

The subroutine sets up a connection to SQL Server using ADO.
It sets up and executes a stored procedure, GetYearlyCalendarInformation, expecting the year as a parameter.
It then uses the .NextRecordset method of the ADODB.Recordset object to move through each recordset returned by the stored procedure.
The .CopyFromRecordset method is used to directly dump the data into Excel worksheets.

This method will generate three new worksheets in your active Excel workbook, each populated with data from one of the recordsets returned by the stored procedure. Adjust the year parameter as necessary for different data sets.

Acknowledgements

Portions of this article's body generated with the help of ChatGPT
One or more code samples generated with the help of ChatGPT