snaps & snippets

So, here's a monkey. A 'monkey' function, that is. The tree we'll be climbing is a self-referenced table:

create table dbo.TreeOfValues
 (
 NodeID   int  primary key
 ,Value   nvarchar(64)
 ,ChildOfNodeID  int 
  foreign key references dbo.TreeOfValues (NodeID)
 )
go

insert dbo.TreeOfValues
 (
 NodeID
 ,Value
 ,ChildOfNodeID
 )
 select 1 as NodeID
  ,'father1' as Value
  ,null as ChildOfNodeID
 union all
 select 2
  ,'son11'
  ,1
 union all
 select 3
  ,'son12'
  ,1
 union all
 select 4
  ,'son13'
  ,1
 union all
 select 5
  ,'father2'
  ,null
 union all
 select 6
  ,'son21'
  ,5
 union all
 select 7
  ,'son22'
  ,5
 union all
 select 8
  ,'grandson211'
  ,6
 union all
 select 9
  ,'grandson212'
  ,6
go

And this is the function:

create function dbo.Monkey
 (
 @NodeID  int
 ,@upToNodeID int
 )
returns int
as
begin
 declare @loopDone bit
 declare @currentNodeID int

 set @loopDone = 0

 while (@loopDone = 0)
  begin
   select @NodeID
     = dbo.TreeOfValues.ChildOfNodeID
    from dbo.TreeOfValues
    where (dbo.TreeOfValues.NodeID = @NodeID)

   select @currentNodeID
     = dbo.TreeOfValues.NodeID
    from dbo.TreeOfValues
    where (dbo.TreeOfValues.NodeID = @NodeID)

   if (@NodeID = @upToNodeID
       or @NodeID is null
       or @currentNodeID is null)
    begin
     set @loopDone = 1
    end
  end

 return @NodeID
end
go

And here is an example to find all the descendants of 'father2':

select *
 from dbo.TreeOfValues
 where (dbo.Monkey(dbo.TreeOfValues.NodeID , 5) = 5)

Beware! This function can get really slow on large tables. Indexes on NodeID and ChildOfNodeID are a necessity. ML