MSSQL Rekursionslevel bestimmen

[Bummi]

@rollstuhlfahrer
man kann so eine Prozedur schon schreiben:

Code:

Create Procedure P_GetTreeDist(@ID1 int,@ID2 int) As
--201011 by Thomas Wassermann
Declare @count int
Declare @dist int

Select Cast(@ID1 as int) as ID,0 as Dist 
into #tmp1

Select Cast(@ID2 as int) as ID,0 as Dist 
into #tmp2

select @Count=0
Select @dist=1
While @Count<(Select Count(*) from #tmp1)
   begin
   Select @Count=(Select Count(*) from #tmp1)
   insert into #tmp1 Select Cast(refparent as int),@dist from  [new_tbl_categories]
   where internalid in (Select ID from #tmp1) and refparent not in (Select ID from #tmp1)
   Select @dist = @dist + 1
   end

select @Count=0
Select @dist=1
While @Count<(Select Count(*) from #tmp2)
   begin
   Select @Count=(Select Count(*) from #tmp2)
   insert into #tmp2 Select Cast(refparent as int),@dist from  [new_tbl_categories]
   where internalid in (Select ID from #tmp2) and refparent not in (Select ID from #tmp2)
   Select @dist = @dist + 1
   end

Select Min(#tmp1.Dist+#tmp2.Dist) as Distance from #tmp1 join #tmp2 on #tmp1.ID = #tmp2.ID

Drop Table #tmp1
Drop Table #tmp2