Get Hierarchy from SQL DATABASE Tables

CREATE PROCEDURE SP_Fin_Get_All_AccountHeads_Group_Hierarchy
    @Group VARCHAR(100),
    @CompID VARCHAR(100)
AS
---SP_Fin_Get_All_AccountHeads_Group_Hierarchy 'Admnistrative Expenses', 'ANOOS'
BEGIN
DECLARE    @primary_account_head_group_id VARCHAR(100)
DECLARE @MaxDepth INT
    DECLARE @CurrentFiscal VARCHAR(50)
    SET @MaxDepth = 6;   
   
    ;WITH LedgerList AS(
                    SELECT isnull(account_heads_parent_id,0) AS ParentId,
                            account_heads_id AS Id,       
                            account_heads_code AS Code,
                            description AS Description,   1 AS Hierarchy
                    FROM fin_account_heads
                    WHERE account_heads_parent_id is null
                    AND primary_account_head_group_id=(select account_heads_group_id    from               fin_account_heads_group where account_heads_group_name =@Group)
                    and company_code = @CompID
                    AND is_active=1
                   
                   UNION ALL

                    SELECT AH.account_heads_parent_id AS ParentId,
                            AH.account_heads_id AS Id,       
                            AH.account_heads_code AS Code,
                            AH.description AS Description,
                            LedgerList.Hierarchy + 1 AS Hierarchy
                    FROM fin_account_heads AH
                    INNER JOIN LedgerList
                                ON AH.account_heads_parent_id = LedgerList.ID
                    WHERE AH.company_code = @CompID
                            AND LedgerList.Hierarchy < @MaxDepth
                    AND is_active=1
                    )   
                    SELECT * FROM LedgerList                   
End