Retrieve data from stored procedure which has multiple result sets

Retrieving Multiple Result Sets from SQL Server Stored Procedures

Learn how to effectively call and process stored procedures that return more than one result set in SQL Server, covering common scenarios and client-side handling.

Stored procedures in SQL Server are powerful tools for encapsulating business logic, improving performance, and enhancing security. A common, yet sometimes challenging, scenario is when a single stored procedure needs to return multiple distinct sets of data. This article will guide you through the process of creating such stored procedures and, crucially, how to retrieve and process these multiple result sets from client applications.

Understanding Multiple Result Sets

A stored procedure can return multiple result sets by simply executing multiple SELECT statements within its body. Each SELECT statement generates a separate result set. This can be useful for scenarios where related but distinct data is needed in a single call, reducing network round trips and simplifying application logic. For example, you might want to retrieve customer details, their recent orders, and their shipping addresses all in one go.

flowchart TD
    A[Client Application] --> B("Execute Stored Procedure");
    B --> C{Stored Procedure Execution};
    C --> D["SELECT Statement 1 (Result Set 1)"];
    C --> E["SELECT Statement 2 (Result Set 2)"];
    C --> F["SELECT Statement 3 (Result Set 3)"];
    D --> G["Return Result Set 1 to Client"];
    E --> H["Return Result Set 2 to Client"];
    F --> I["Return Result Set 3 to Client"];
    G & H & I --> J[Client Processes All Result Sets];

Flow of retrieving multiple result sets from a stored procedure.

Creating a Stored Procedure with Multiple Result Sets

Let's start by defining a simple stored procedure that returns two different result sets: a list of products and a list of categories. This example will use a hypothetical Products and Categories table.

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(100),
    Price DECIMAL(10, 2),
    CategoryID INT
);

CREATE TABLE Categories (
    CategoryID INT PRIMARY KEY,
    CategoryName NVARCHAR(50)
);

INSERT INTO Categories (CategoryID, CategoryName) VALUES
(1, 'Electronics'),
(2, 'Books'),
(3, 'Home Goods');

INSERT INTO Products (ProductID, ProductName, Price, CategoryID) VALUES
(101, 'Laptop', 1200.00, 1),
(102, 'SQL Server Book', 45.50, 2),
(103, 'Coffee Maker', 75.00, 3),
(104, 'Smartphone', 800.00, 1);
GO

CREATE PROCEDURE GetProductAndCategoryData
AS
BEGIN
    -- Result Set 1: All Products
    SELECT ProductID, ProductName, Price, CategoryID
    FROM Products;

    -- Result Set 2: All Categories
    SELECT CategoryID, CategoryName
    FROM Categories;
END;
GO

SQL script to create tables and a stored procedure returning multiple result sets.

Retrieving Multiple Result Sets in C# (ADO.NET)

When working with ADO.NET in C#, you use a SqlDataReader to iterate through the result sets. The key method here is NextResult(), which advances the reader to the next available result set.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;

public class DataRetriever
{
    public static void Main(string[] args)
    {
        string connectionString = "Data Source=.;Initial Catalog=YourDatabase;Integrated Security=True";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlCommand command = new SqlCommand("GetProductAndCategoryData", connection);
            command.CommandType = CommandType.StoredProcedure;

            try
            {
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                // Process Result Set 1 (Products)
                Console.WriteLine("--- Products ---");
                while (reader.Read())
                {
                    Console.WriteLine($"ID: {reader["ProductID"]}, Name: {reader["ProductName"]}, Price: {reader["Price"]}");
                }

                // Move to the next result set
                if (reader.NextResult())
                {
                    // Process Result Set 2 (Categories)
                    Console.WriteLine("\n--- Categories ---");
                    while (reader.Read())
                    {
                        Console.WriteLine($"ID: {reader["CategoryID"]}, Name: {reader["CategoryName"]}");
                    }
                }
                else
                {
                    Console.WriteLine("\nNo second result set found.");
                }

                reader.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine($"An error occurred: {ex.Message}");
            }
        }
    }
}

C# code demonstrating how to retrieve and process multiple result sets using SqlDataReader.NextResult().

Retrieving Multiple Result Sets in Python (pyodbc)

For Python applications connecting to SQL Server, the pyodbc library is commonly used. Similar to ADO.NET, pyodbc cursors provide a way to iterate through multiple result sets.

import pyodbc

# Connection string for SQL Server
conn_str = (
    "DRIVER={ODBC Driver 17 for SQL Server};"
    "SERVER=localhost;DATABASE=YourDatabase;UID=YourUser;PWD=YourPassword"
)

try:
    cnxn = pyodbc.connect(conn_str)
    cursor = cnxn.cursor()

    # Execute the stored procedure
    cursor.execute("{CALL GetProductAndCategoryData}")

    # Process Result Set 1 (Products)
    print("--- Products ---")
    for row in cursor:
        print(f"ID: {row.ProductID}, Name: {row.ProductName}, Price: {row.Price}")

    # Move to the next result set
    if cursor.nextset():
        # Process Result Set 2 (Categories)
        print("\n--- Categories ---")
        for row in cursor:
            print(f"ID: {row.CategoryID}, Name: {row.CategoryName}")
    else:
        print("\nNo second result set found.")

    cursor.close()
    cnxn.close()

except pyodbc.Error as ex:
    sqlstate = ex.args[0]
    print(f"Database error: {sqlstate}")

Python code using pyodbc to retrieve and process multiple result sets.

Best Practices and Considerations

While returning multiple result sets can be convenient, consider the following best practices:

• Clarity and Maintainability: Ensure the stored procedure's purpose remains clear. If it returns too many disparate result sets, it might be better to split it into multiple, more focused procedures.
• Performance: While it reduces round trips, a very complex stored procedure with many SELECT statements might still be slow. Profile your procedures to identify bottlenecks.
• Client-Side Logic: The client application must be prepared to handle the order and structure of each result set. Any change in the stored procedure's SELECT statements (order, columns) will require corresponding client-side updates.
• Error Handling: Implement robust error handling on both the SQL Server side (e.g., TRY...CATCH blocks) and the client side to gracefully manage issues during execution or data retrieval.