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)
        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

1 comment :

xiw3g7uivh said...

Metal fabrication has purposes in broad variety|all kinds} of industries. Because of the versatility of instruments and processes, it’s used to create components for industries as diversified as agriculture, spas, army craft and automobiles. We are Architectural day dreamer baby lounger Fabrication, a longtime shade solutions firm situated in Fort Worth, Texas.