Styles

Tuesday, August 23, 2011

MSSQL recursive list in a Stored Procedure

The following scenario allows one Staff Manager to view all direct reports and their child member's direct reports recursively:

    WITH Hierachy(StaffNo, ManagerStaffNo, PreferredName, Level)
    AS
    (
        SELECT StaffNo, ManagerStaffNo, PreferredName, 0 AS Level
        FROM Staff s
        WHERE s.StaffNo = @StaffNo
        UNION ALL
        SELECT s.StaffNo, s.ManagerStaffNo, s.PreferredName, sh.Level + 1
        FROM Staff s
        INNER JOIN Hierachy sh ON s.ManagerStaffNo = sh.StaffNo
    )
    SELECT StaffNo, ManagerStaffNo, PreferredName, Level
    FROM Hierachy