Issue with NextResultSet() when Handling Multi-ResultSets with 1 Row 1 Column in Stored Procedure

[yusren]

I am encountering an issue with the github.com/microsoft/go-mssqldb driver while working with multi-result sets in a stored procedure. Specifically, when the first result set consists of a single row with multiple columns (e.g., an aggregate value), and the second result set contains multiple rows and columns, the NextResultSet() method fails to move to the second result set.

Issue Details:

• Stored Procedure Structure:

  • The first result set contains 1 row with 1 column (e.g., a single integer value or aggregate value).
  • The second result set contains multiple rows and columns (e.g., a list of user drafts).

• What Happens:

  • When the query is executed, the first result set (which contains a single row with 1 column) is scanned correctly.
  • However, the driver seems unable to proceed to the second result set, even when NextResultSet() is called. It fails to move past the first result set and doesn't return any data from the second result set.

• Workaround:

  • When I swap the result sets so that the first result set contains multiple rows and columns, and the second result set contains the single row with a column, the issue disappears and the NextResultSet() successfully moves to the second record set.

Steps to Reproduce:

1. Execute a stored procedure that returns two result sets:

   • Result Set 1: 1 row with 1 column (e.g., a total count, aggregate data, or a status).
   • Result Set 2: Multiple rows and columns (e.g., user data or a list of drafts).

   Example stored procedure:

   CREATE PROCEDURE dbo.sp_user_get_draft_list
   AS
   BEGIN
       -- First result set (1 row, 1 column)
       SELECT COUNT(*) AS total FROM users;
       
       -- Second result set (multiple rows, multiple columns)
       SELECT id, username, email FROM users;
   END

2. In Go code, use the NextResultSet() method after scanning the first result set to try to access the second result set:

   if recordSet.Next() {
       // Scan first record set (single row with multiple columns)
       err := recordSet.Scan(&totalRow)
       if err != nil {
           return nil, fmt.Errorf("failed to scan total row: %w", err)
       }
   }
   
   if recordSet.NextResultSet() {
       // Scan second record set (multiple rows)
       for recordSet.Next() {
           // Scan data from the second result set
       }
   } else {
       return nil, fmt.Errorf("failed to move to the second result set")
   }

3. When the first result set contains only 1 row with 1 column, the NextResultSet() call does not seem to proceed to the second result set.

Expected Behavior:

The NextResultSet() method should correctly handle the transition from the first result set (which contains 1 row with 1 column) to the second result set (which contains multiple rows and columns).

Observed Behavior:

The NextResultSet() method fails to move past the first result set when the first result set contains only 1 row with 1 column. However, swapping the result sets (making the first result set contain multiple rows and columns) resolves the issue.

Additional Information:

• I am using github.com/microsoft/go-mssqldb version 1.9.2.
• The issue occurs specifically when dealing with 1 row 1 column result sets.
• I have captured the query being executed and can provide it for further analysis if needed.

Query Captured:

EXEC dbo.sp_user_get_draft_list @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8

I am unsure whether this is a bug in the driver or if it is due to how the driver handles minimal result sets (e.g., a single row with multiple columns). I would appreciate any assistance or insight into how to resolve this issue.

Thank you for your time and help!

[shueybubbles]

thx for opening an issue!
Can you try reproducing the issue using the (go-sqlcmd)[https://github.com/microsoft/go-sqlcmd] application? go-sqlcmd uses the message queue based query model instead of the standard looping model.
Or try changing your app to use the message queue loop instead and see if it works.
It would be a helpful data point for debugging.

[yusren]

Thank you for the suggestion!

I successfully reproduced the issue using the go-sqlcmd tool, and I was able to retrieve the correct data. The message queue-based query model worked as expected in this case. I have saved a screenshot of the result for reference.

I plan to modify the query handling by placing the results of multi-column, multi-row queries at the beginning of the return result, and the query with a single column and a single row at the end. Since I am working towards a tight deadline, the application needs to be ready for testing as soon as possible.
Thanks in advance

[ChanRolex86]

TL;DR: I hit this same issue (procs with 2–5 result sets, including single-row and zero-row outputs) and was able to "fix" it using the same approach of strategically ordering the result sets. After reproducing it in a clean Docker + test DB setup and adding tests to queries_test.go, it turns out the driver is behaving as expected: you must fully iterate the current result set with rows.Next() before calling rows.NextResultSet(). In practice that means always using a for rows.Next() loop, even when you only expect a single row. If you call Next() just once and then jump straight to NextResultSet(), the driver hasn’t yet seen the metadata for the next result set, so NextResultSet() can (correctly) return false even when more result sets exist. Using the standard database/sql pattern (exhaust each result set with Next(), then move on with NextResultSet()) works reliably.

---

I’ve been looking into this issue because I was hitting the same behaviour in a work codebase: stored procedures returning multiple result sets (typically 2–5) with various combinations of single-row, multi-row, and zero-row outputs. Applying the same workaround of strategically ordering the result sets allowed for the bypassing of the issue.

I’ve now reproduced the behaviour on a separate personal setup using Docker + a test database and dug into it more systematically.

---

Environment

• Go: 1.25.1
• Driver: github.com/microsoft/go-mssqldb (current main)
• SQL Server: 2022 (Docker image mcr.microsoft.com/mssql/server:2022-latest)
• Database: simple test DB go_mssqldb_test with a stored procedure:

CREATE OR ALTER PROCEDURE dbo.sp_user_get_draft_list AS
BEGIN
    SET NOCOUNT ON;

    -- First result set: 1 row, 1 column
    SELECT COUNT(*) AS total
    FROM dbo.Users;

    -- Second result set: multiple rows / multiple columns
    SELECT Id, Username, Email
    FROM dbo.Users
    ORDER BY Id;
END;

---

Failing usage pattern

This is the pattern I originally used (both at work and in the repro):

db, err := sql.Open("sqlserver", connStr)
if err != nil {
    log.Fatal(err)
}
defer db.Close()

ctx := context.Background()

rows, err := db.QueryContext(ctx, "EXEC dbo.sp_user_get_draft_list")
if err != nil {
    log.Fatal("QueryContext:", err)
}
defer rows.Close()

// Only read a single row from the first result set
if rows.Next() {
    var total int
    if err := rows.Scan(&total); err != nil {
        log.Fatal("Scan total:", err)
    }
    fmt.Println("Total:", total)
}
if err := rows.Err(); err != nil {
    log.Fatal("Err after first result:", err)
}

// Then try to move to the next result set
hasMore := rows.NextResultSet()
fmt.Println("NextResultSet returned:", hasMore)
if !hasMore {
    fmt.Println("No second result set detected")
    return
}

// (this part never runs)
for rows.Next() {
    var id int
    var username, email string
    if err := rows.Scan(&id, &username, &email); err != nil {
        log.Fatal("Scan second result:", err)
    }
    fmt.Printf("User: id=%d username=%s email=%s\n", id, username, email)
}
if err := rows.Err(); err != nil {
    log.Fatal("Err after second result:", err)
}

Output from the repro program:

=== Failing pattern (single Next, then NextResultSet) ===
Total (first result set): 3
NextResultSet returned: false
No second result set detected

This exactly matches what I was seeing in the original environment: the second result set “disappears” when the first one is a 1 row / 1 column result set and you only call Next() once before NextResultSet().

---

Tests added in a local clone

To dig deeper, in a local clone of this repo I added the following test to queries_test.go:

func TestNextResultSet_SingleRowThenMulti(t *testing.T) {
    conn, logger := open(t)
    defer conn.Close()
    defer logger.StopLogging()

    tx, err := conn.Begin()
    if err != nil {
        t.Fatal("Begin tran failed", err)
    }
    defer tx.Rollback()

    // ----------------------------------------------------------------------
    // Setup: temp table with a few rows
    // ----------------------------------------------------------------------
    _, err = tx.Exec(`
        create table #NextResultSetTestUsers (
            Id int identity(1,1) primary key,
            Username nvarchar(100) not null,
            Email nvarchar(255) not null
        )
    `)
    if err != nil {
        t.Fatal("create temp table failed:", err)
    }

    _, err = tx.Exec(`
        insert into #NextResultSetTestUsers (Username, Email)
        values (N'alice', N'alice@example.com'),
               (N'bob',   N'bob@example.com'),
               (N'carol', N'carol@example.com')
    `)
    if err != nil {
        t.Fatal("insert into temp table failed:", err)
    }

    // ----------------------------------------------------------------------
    // Setup: temporary stored procedure returning 2 result sets
    //   1) single row / single column (COUNT(*))
    //   2) multiple rows / multiple columns (Id, Username, Email)
    // ----------------------------------------------------------------------
    // Drop any existing temp proc in this session (harmless if it doesn't exist).
    _, _ = tx.Exec(`drop procedure #sp_nextresultset_test`)

    _, err = tx.Exec(`
        create procedure #sp_nextresultset_test
        as
        begin
            set nocount on;

            -- First result set: 1 row, 1 column
            select count(*) as total
            from #NextResultSetTestUsers;

            -- Second result set: multiple rows / columns
            select Id, Username, Email
            from #NextResultSetTestUsers
            order by Id;
        end;
    `)
    if err != nil {
        t.Fatal("create temp procedure failed:", err)
    }

    // ----------------------------------------------------------------------
    // Act: execute the temp procedure and walk the result sets
    // ----------------------------------------------------------------------
    rows, err := tx.Query(`exec #sp_nextresultset_test`)
    if err != nil {
        t.Fatal("exec temp procedure failed:", err)
    }
    defer rows.Close()

    // First result set: expect exactly one row with the count
    if !rows.Next() {
        t.Fatal("expected a row in first result set, got none")
    }
    var total int
    if err := rows.Scan(&total); err != nil {
        t.Fatal("scan total failed:", err)
    }
    if total != 3 {
        t.Errorf("expected total 3, got %d", total)
    }
    if err := rows.Err(); err != nil {
        t.Fatal("rows.Err after first result set:", err)
    }

    // Move to second result set
    if !rows.NextResultSet() {
        t.Fatal("expected second result set, but NextResultSet returned false")
    }

    // Second result set: expect at least one row
    if !rows.Next() {
        t.Fatal("expected at least one row in second result set, got none")
    }
    var (
        id       int
        username string
        email    string
    )
    if err := rows.Scan(&id, &username, &email); err != nil {
        t.Fatal("scan first row of second result set failed:", err)
    }

    // Optionally consume remaining rows to ensure iteration works
    for rows.Next() {
        if err := rows.Scan(&id, &username, &email); err != nil {
            t.Fatal("scan remaining rows in second result set failed:", err)
        }
    }
    if err := rows.Err(); err != nil {
        t.Fatal("rows.Err after second result set:", err)
    }
}

With logging enabled, the test output showed that SQL Server did in fact send both result sets (tokens for ColMetadata, Row, DoneInProc, etc. for each set), and the procedure execution completed successfully:

exec #sp_nextresultset_test
got token tokenColMetadata
got token tokenRow
got token tokenDoneInProc
got token tokenColMetadata
got token tokenOrder
got token tokenRow
got token tokenRow
got token tokenRow
got token tokenDoneInProc
got token tokenReturnStatus
got token tokenDoneProc
got DONE or DONEPROC status=0

However, the test still failed on:

expected second result set, but NextResultSet returned false

This led me to try the standard database/sql pattern of fully exhausting the first result set before calling NextResultSet().

---

Working usage pattern (alternate test)

In the same local clone, I then added an alternate test, also in queries_test.go:

func TestNextResultSet_SingleRowThenMultiAlt(t *testing.T) {
    conn, logger := open(t)
    defer conn.Close()
    defer logger.StopLogging()

    ctx := context.Background()

    tx, err := conn.BeginTx(ctx, nil)
    if err != nil {
        t.Fatal("BeginTx failed:", err)
    }
    defer tx.Rollback()

    // Temp table for the test
    _, err = tx.ExecContext(ctx, `
        create table #NextResultSetTestUsers (
            Id int identity(1,1) primary key,
            Username nvarchar(100) not null,
            Email nvarchar(255) not null
        )
    `)
    if err != nil {
        t.Fatal("create temp table failed:", err)
    }

    _, err = tx.ExecContext(ctx, `
        insert into #NextResultSetTestUsers (Username, Email)
        values (N'alice', N'alice@example.com'),
               (N'bob',   N'bob@example.com'),
               (N'carol', N'carol@example.com')
    `)
    if err != nil {
        t.Fatal("insert into temp table failed:", err)
    }

    // Best-effort cleanup of any previous temp proc with same name
    _, _ = tx.ExecContext(ctx, `drop procedure #sp_nextresultset_test`)

    _, err = tx.ExecContext(ctx, `
        create procedure #sp_nextresultset_test
        as
        begin
            set nocount on;

            -- First result set: 1 row, 1 column
            select count(*) as total
            from #NextResultSetTestUsers;

            -- Second result set: multiple rows / columns
            select Id, Username, Email
            from #NextResultSetTestUsers
            order by Id;
        end;
    `)
    if err != nil {
        t.Fatal("create procedure failed:", err)
    }

    rows, err := tx.QueryContext(ctx, `exec #sp_nextresultset_test`)
    if err != nil {
        t.Fatal("exec proc failed:", err)
    }
    defer rows.Close()

    // ---- First result set ----
    var totals []int
    for rows.Next() {
        var total int
        if err := rows.Scan(&total); err != nil {
            t.Fatal("scan first result set failed:", err)
        }
        totals = append(totals, total)
    }
    if err := rows.Err(); err != nil {
        t.Fatal("rows.Err for first result set:", err)
    }

    if len(totals) != 1 {
        t.Fatalf("expected 1 row in first result set, got %d", len(totals))
    }
    if totals[0] != 3 {
        t.Fatalf("expected total=3 in first result set, got %d", totals[0])
    }

    // ---- Second result set ----
    if !rows.NextResultSet() {
        t.Fatalf("expected second result set, but NextResultSet returned false")
    }

    type userRow struct {
        id       int
        username string
        email    string
    }
    var users []userRow

    for rows.Next() {
        var u userRow
        if err := rows.Scan(&u.id, &u.username, &u.email); err != nil {
            t.Fatal("scan second result set failed:", err)
        }
        users = append(users, u)
    }
    if err := rows.Err(); err != nil {
        t.Fatal("rows.Err for second result set:", err)
    }

    if len(users) != 3 {
        t.Fatalf("expected 3 rows in second result set, got %d", len(users))
    }
}

This test passes on my setup.

In other words, using the usual database/sql pattern:

for rows.Next() {
    // scan current result set
}
if err := rows.Err(); err != nil { ... }

for rows.NextResultSet() {
    for rows.Next() {
        // scan next result set
    }
    if err := rows.Err(); err != nil { ... }
}

multiple result sets (including the “1 row / 1 column then multi-row” scenario) work correctly with go-mssqldb.

I’ll also be testing this pattern on the original work environment with more complex stored procedures (2–5 result sets, including zero-row and single-row combinations), but the same pattern should apply there as well: whenever I expect a single row, I can use if rows.Next() for that case, and when I need to move between result sets I make sure the previous Next() loop has been fully driven to completion.

---

Conclusion / suggestion

From what I can see so far, the driver is behaving consistently with how database/sql expects Next() / NextResultSet() to be used:

• You must fully iterate the current result set with Next() before expecting NextResultSet() to return true.
• If you only call Next() once and then jump straight to NextResultSet(), the driver has not yet had a chance to see the metadata for the next result set on the wire, so NextResultSet() correctly reports false.