I have a table CREATE TABLE [dbo].[chart_hiera2]( [AccessID] [int] NULL, [ChildID] [int] NULL, [Child] [varchar](100) NULL, [ParentID] [int] NULL, [CGID] [int] NULL, [Depth] [smallint] NULL, [Lineage] [varchar](255) NULL, [node] [bit] NULL, [PercentOwnership] [varchar](10) NULL, [Notes] [varchar](80) NULL ) ON [PRIMARY] I am trying to build the value of hierarchy that will be later inserted in the linage and Depth column. I am trying to do so using recursion one. the rulles for recording the linages as as follows Lineage = parent.Lineage + Ltrim(Str(ParentID,6,0)) + '/' here is my code below with BuildHierarchy as ( SELECT AN.AccessID,AN.ChildID,AN.Child,AN.ParentID,AN.CGID, 1 as Depth,AN.Lineage,AN.node,AN.PercentOwnership, AN.Notes FROM chart_hiera2 as AN WHERE AN.Depth Is Null and AN.AccessID = @accID union all SELECT AN.AccessID,AN.ChildID,AN.Child,AN.ParentID,AN.CGID,Cast(AN.Depth as smallint) +1 ,Cast(BH.Lineage+ Ltrim(Str(AN.ParentID,6,0)) + '/' as varchar(255)),AN.node,AN.PercentOwnership, AN.Notes FROM chart_hiera2 as AN inner join BuildHierarchy BH on AN.ParentID=BH.ChildID WHERE AN.Depth>=0 AND AN.Lineage Is Not Null AND AN.Depth Is Null and AN.AccessID = @accID)--@accID --and T.AccessID = @accID) select * from BuildHierarchy but it does not increment Depth or builds Lineage .What am I doing wrong?