SQL Server Performance

recursion problem

Discussion in 'General Developer Questions' started by ilay golferd, Mar 28, 2008.

  1. ilay golferd New Member

    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?
  2. satya Moderator

    Do you get any error or warning when doing this>

Share This Page