-- Fist variant
CREATE FUNCTION dbo.node_level ( @id int )
RETURNS INT AS  
BEGIN
   DECLARE @R INT, @PAR INT
   SELECT @PAR = ParentID FROM Nodes WHERE NodeID=@id
   IF (@par = 0) OR (@par is null)
   BEGIN
      SET @R = 0
   END ELSE BEGIN
      SET @R = dbo.node_level ( @PAR ) + 1
   END
   RETURN @R
END

-- second variant
--функция возвращает "уровень" узла в дереве

ALTER FUNCTION dbo.Node_Level ( @ID INT )
RETURNS INT AS  
BEGIN
  DECLARE @Parent_ID INT, @Res INT

  SET @Res = -1
  SET @Parent_ID = -1

  WHILE (@Parent_ID IS NOT NULL)
    BEGIN
      SELECT @Parent_ID = Parent_ID FROM TD_Group WHERE ID_Group = @ID
      SET @ID = @Parent_ID
      SET @Res = @Res + 1
    END

  RETURN @Res
END




originals

declare @tree table (parent int, child int )
insert into @tree values(1,2)
insert into @tree values(1,8)
insert into @tree values(1,4)
insert into @tree values(1,6)
insert into @tree values(1,7)
insert into @tree values(2,5)
insert into @tree values(8,5)
insert into @tree values(4,5)
insert into @tree values(5,3)
insert into @tree values(6,3)
insert into @tree values(7,9)
insert into @tree values(3,10)
insert into @tree values(9,10)

declare @elem int, @level int
select @elem=5, @level=0       -- @elem - элемент, с которого начинаем поиск подузлов

declare @wave table (parent int null, child int null, level int)

insert into @wave
select *, @level
from @tree
where parent=@elem

while @@rowcount>0
begin

select @level=@level+1

insert into @wave
select t.child, coalesce(t.parent,w.parent), @level
from @wave w
left join @tree t on w.parent=t.parent
where w.level=@level-1 and w.parent is not null

end

select distinct child from @wave

http://www.sql.ru/forum/actualutils.aspx?action=gotomsg&tid=207456&msg=1782565

create table tree (PID int, ID int )



-- Все потомки @ID, включая родителя @ID

create function Childs(@ID int)

returns @t table (id int not null primary key, level int not null, UNIQUE CLUSTERED(level, id))

AS begin

  declare @level int

  set @level=0 



  insert into @t VALUES(@ID,0)



  while @@rowcount>0 begin

    set @level=@level+1

    insert into @t

      select t.ID, @level

      from @t w inner join tree t on w.ID=t.PID

     where w.level=@level-1

  end

end

Привожу функцию, которая проверяет есть ли у данной ветки @ID, хоть где-то в ирархии парент @CHID.



CREATE FUNCTION CheckParent (@ID int, @CHID int)

RETURNS int

AS

BEGIN

declare @TID uniqueidentifier

set @TID=@ID

while 1=1

begin

select @TID=PID from TREE_TABLE where ID=@TID

if (@TID=@CHID) return 1

if (@TID is null) or (@TID=@ID) return 0

end

return 0

END



С помощью этой функции можно например удалить всех детей определенной ветки, например удалить всех детей ветки с кодом @PID:



DELETE FROM TREE_TABLE WHERE CheckParent(ID, @PID)=1

www.sql.ru/forum/actualthread.aspx

Recursive same-table query in SQL Server 2008

    DECLARE @tbl TABLE (

         Id INT

        ,[Name] VARCHAR(20)

        ,ParentId INT

        )

    INSERT INTO @tbl( Id, Name, ParentId )

    VALUES

     (1, 'Europe', NULL)

    ,(2, 'Asia',   NULL)

    ,(3, 'Germany', 1)

    ,(4, 'UK',      1)

    ,(5, 'China',   2)

    ,(6, 'India',   2)

    ,(7, 'Scotland', 4)

    ,(8, 'Edinburgh', 7)

    ,(9, 'Leith', 8)

    ;

WITH  abcd

        AS (

              -- anchor

            SELECT  id, [Name], ParentID,

                    CAST(([Name]) AS VARCHAR(1000)) AS "Path"

            FROM    @tbl

            WHERE   ParentId IS NULL

            UNION ALL

              --recursive member

            SELECT  t.id, t.[Name], t.ParentID,

                    CAST((a.path + '/' + t.Name) AS VARCHAR(1000)) AS "Path"

            FROM    @tbl AS t

                    JOIN abcd AS a

                      ON t.ParentId = a.id

           )

SELECT * FROM abcd