Recursive Stored Procedures

Stored Procedures > Recursive Stored Procedures

Recursive stored procedures in SQL Server are procedures that call themselves. This technique is particularly useful for querying hierarchical data structures, such as organizational charts, bill of materials, or file system directories. By repeatedly executing the same logic, you can traverse deeply nested data relationships.

Understanding Recursion

A recursive procedure needs two main components:

  1. Base Case: A condition that stops the recursion. Without a base case, the procedure would call itself infinitely, leading to a stack overflow error.
  2. Recursive Step: The part where the procedure calls itself, typically with modified parameters that move it closer to the base case.

Example: Hierarchical Data

Consider a simple employee hierarchy where each employee reports to a manager. We can use a recursive stored procedure to find all subordinates of a given employee, or to list the entire reporting chain upwards.

Sample Table: Employees


CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(100),
    ManagerID INT NULL
);

INSERT INTO Employees (EmployeeID, EmployeeName, ManagerID) VALUES
(1, 'CEO', NULL),
(2, 'VP Sales', 1),
(3, 'VP Engineering', 1),
(4, 'Sales Manager', 2),
(5, 'Engineer 1', 3),
(6, 'Engineer 2', 3),
(7, 'Sales Rep 1', 4),
(8, 'Sales Rep 2', 4);

Recursive Stored Procedure: GetSubordinates

This procedure finds all direct and indirect subordinates of a given employee.


CREATE PROCEDURE GetSubordinates
    @EmployeeID INT
AS
BEGIN
    SET NOCOUNT ON;

    -- Anchor Member: Select the current employee
    SELECT
        E.EmployeeID,
        E.EmployeeName,
        0 AS Level
    FROM Employees AS E
    WHERE E.EmployeeID = @EmployeeID

    UNION ALL

    -- Recursive Member: Select subordinates of the current employee
    SELECT
        E.EmployeeID,
        E.EmployeeName,
        Subordinates.Level + 1
    FROM Employees AS E
    INNER JOIN GetSubordinates AS Subordinates
        ON E.ManagerID = Subordinates.EmployeeID
    WHERE Subordinates.Level < MAX_RECURSION_DEPTH; -- Optional: Limit recursion depth

END;

Important: The sample above uses a common pattern for recursion, but in modern SQL Server versions (2005+), the preferred and more efficient way to handle hierarchical data is using Common Table Expressions (CTEs) with recursion.

Using Common Table Expressions (CTEs) for Recursion

CTEs provide a cleaner and more structured approach to recursion.

Example: GetEmployeeHierarchy with CTE

This CTE-based procedure lists all employees and their respective levels in the hierarchy, starting from a specified employee.


CREATE PROCEDURE GetEmployeeHierarchy
    @EmployeeID INT
AS
BEGIN
    SET NOCOUNT ON;

    WITH EmployeeHierarchy (EmployeeID, EmployeeName, ManagerID, Level)
    AS
    (
        -- Anchor Member: The starting employee
        SELECT
            E.EmployeeID,
            E.EmployeeName,
            E.ManagerID,
            0 AS Level
        FROM Employees AS E
        WHERE E.EmployeeID = @EmployeeID

        UNION ALL

        -- Recursive Member: Fetch direct reports
        SELECT
            E.EmployeeID,
            E.EmployeeName,
            E.ManagerID,
            EH.Level + 1
        FROM Employees AS E
        INNER JOIN EmployeeHierarchy AS EH
            ON E.ManagerID = EH.EmployeeID
        WHERE EH.Level < MAX_RECURSION_DEPTH -- Optional: Limit depth
    )
    SELECT
        EmployeeID,
        EmployeeName,
        Level
    FROM EmployeeHierarchy
    ORDER BY Level, EmployeeName;

END;

Executing the CTE Procedure


EXEC GetEmployeeHierarchy @EmployeeID = 1;

Considerations for Recursive Procedures

For very large or complex hierarchies, consider alternative data structures like Adjacency Lists, Nested Sets, or Materialized Paths, which might offer better performance for certain operations.

Related Topics