Question

  Asked: Mar 28 2008   8:32 PM GMT
  Asked by: Ilay

help with t-sql recursion

I have a table

CREATE TABLE [dbo].[chart_hiera2](
[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?