咨询递归存储过程（Recursive Stored Procedures）的适用场景及优质示例

Great question! Recursive stored procedures are super handy when you're dealing with hierarchical or nested data structures—they let you traverse these structures cleanly without writing messy iterative loops. Let me break down the common use cases first, then share a solid, practical example that’s easy to follow.

• Hierarchical Data Traversal: The most frequent use case—think company org charts (employee → manager → director), file system directories, or e-commerce product category trees. Recursion lets you drill down through every level of the hierarchy with minimal code.
• Multi-Level Calculations: Scenarios like calculating tiered referral commissions (referrer → level 1 → level 2 → level 3) or cumulative discounts across nested product groups benefit from recursive logic.
• Graph Traversal: For navigating interconnected data like social media friend chains, logistics route nodes, or network topology maps, recursion can follow links between nodes dynamically.
• Dynamic Query Building: When you need to generate queries based on nested permissions or access levels, recursion can build filter conditions incrementally as it traverses each tier.

Let’s create a recursive stored procedure to fetch all subordinates (including nested levels) of a given manager, with formatted output to visualize the hierarchy.

Step 1: Create Test Data Table #

First, set up a simple employee table with hierarchical relationships:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100),
    ManagerID INT NULL, -- NULL = top-level executive (no manager)
    JobTitle VARCHAR(100)
);

INSERT INTO Employees VALUES
(1, 'John Doe', NULL, 'CEO'),
(2, 'Jane Smith', 1, 'CTO'),
(3, 'Bob Johnson', 2, 'Engineering Manager'),
(4, 'Alice Williams', 3, 'Senior Developer'),
(5, 'Charlie Brown', 3, 'Junior Developer'),
(6, 'Emily Davis', 1, 'CFO'),
(7, 'Frank Miller', 6, 'Finance Manager');

Step 2: Recursive Stored Procedure #

This procedure will take a manager ID and return all their direct and indirect subordinates, with indentation to show hierarchy levels:

CREATE PROCEDURE GetAllSubordinates
    @ManagerID INT,
    @IndentLevel INT = 0 -- Optional: Controls indentation for hierarchy visualization
AS
BEGIN
    SET NOCOUNT ON; -- Boost performance by suppressing row count messages

    -- Base Case: Fetch direct reports of the current manager
    SELECT
        EmployeeID,
        Name,
        JobTitle,
        REPLICATE('  ', @IndentLevel) + Name AS HierarchicalName -- Indent to show level
    FROM Employees
    WHERE ManagerID = @ManagerID;

    -- Recursive Case: Call this procedure for each direct report to get their subordinates
    DECLARE @EmpID INT;
    DECLARE emp_cursor CURSOR FOR
        SELECT EmployeeID FROM Employees WHERE ManagerID = @ManagerID;

    OPEN emp_cursor;
    FETCH NEXT FROM emp_cursor INTO @EmpID;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Recurse with the next level of indentation
        EXEC GetAllSubordinates @EmpID, @IndentLevel + 1;
        FETCH NEXT FROM emp_cursor INTO @EmpID;
    END

    CLOSE emp_cursor;
    DEALLOCATE emp_cursor;
END

Step 3: Execute the Procedure #

To get all subordinates of the CTO (EmployeeID 2):

EXEC GetAllSubordinates @ManagerID = 2;

Sample Output #

You’ll get a formatted result that clearly shows the hierarchy:

EmployeeID | Name           | JobTitle               | HierarchicalName
-----------|----------------|------------------------|-------------------
3          | Bob Johnson    | Engineering Manager    |   Bob Johnson
4          | Alice Williams | Senior Developer       |     Alice Williams
5          | Charlie Brown  | Junior Developer       |     Charlie Brown

Key Advantages of This Example #

• Readability: The indentation makes the hierarchy instantly understandable.
• Flexibility: Adjust @IndentLevel to control formatting, or remove it if you don’t need visual hierarchy.
• Clear Termination: The recursion stops automatically when a manager has no direct reports (the cursor returns no rows, so no further calls are made).
• Reusability: Call this procedure anytime you need to fetch subordinates for any manager in the org.

• Recursion Depth Limits: Most databases have a default recursion depth limit (e.g., SQL Server uses 100). For deep hierarchies, use OPTION (MAXRECURSION n) to adjust this.
• Performance: For extremely large datasets, recursive CTEs (Common Table Expressions) might be faster, but stored procedures offer better encapsulation for repeated use.
• Termination Check: Always ensure your procedure has a clear base case to avoid infinite recursion.

内容的提问来源于stack exchange，提问作者Suraj K Mad