Wednesday, October 21, 2009

Get all child business units by level based on specified BU id

To retrieve all child business units by level based on specified business unit id:

ALTER FUNCTION [dbo].[f_GetAllBusinessUnitByID]
(    
    @BUID VARCHAR(MAX)
)
RETURNS TABLE 
AS
RETURN 
(
    WITH BUTree(ParentBusinessUnitId, BusinessUnitId, Name, Level)
    AS
    (
        -- Anchor member definition
        SELECT P.ParentBusinessUnitId, P.BusinessUnitId, 

               P.Name, 0 AS Level
        FROM BusinessUnit AS P
        WHERE P.BusinessUnitId = @BUID

        UNION ALL

        -- Recursive member definition
        SELECT P.ParentBusinessUnitId, P.BusinessUnitId, 

               P.Name, Level + 1
        FROM BusinessUnit AS P
        INNER JOIN BUTree AS B
            ON P.ParentBusinessUnitId = B.BusinessUnitId
    )

    -- Query All Child Node
    SELECT C.ParentBusinessUnitId, C.BusinessUnitId, 

           C.Name, C.Level
    FROM  BUTree C
    WHERE C.BusinessUnitId NOT IN 

          (SELECT ParentBusinessUnitId 
           FROM   BUTree
           WHERE  ParentBusinessUnitId IS NOT NULL)
)

No comments:

Post a Comment