MSSQL Rekursionslevel bestimmen

[Bummi]

Ich habe mir den Spass gemacht

Kannst Du jetzt über

Code:

 
Select a,b,c,(Select Dist from F_GetTreeDist(x,y)) as Dist
from Irgendwas

aufrufen..

Code:

Create FUNCTION F_GetTreeDist(@ID1 int,@ID2 int) 
--201011 by Thomas Wassermann
RETURNS 
@tab TABLE 
(
Ref int,
ID int,
dist int
)
AS
BEGIN
Declare @count int
Declare @dist int
Declare @ref int


Insert into @tab (Ref,ID,Dist) Values (1,@ID1,0)

Insert into @tab (Ref,ID,Dist) Values (2,@ID2,0)

Select @ref = 1

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

select @Count=0
Select @dist=1
Select @ref = 2


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

Insert Into @Tab (Ref,Dist)
Select 3,  Min(t1.Dist+t2.Dist) 
from @tab t1 join @tab t2 on t1.ID = t2.ID
where t1.Ref=1 and t2.Ref=2

Delete @tab where Ref<3
   RETURN 
END
GO