Recursive loop never ends
-
I've written a simple recursive loop that checks for a min() value until it returns null then stops. The problem I'm having is that the server loops on the final value and won't stop.
The data is essentially four rows with Placement values of 1-4 all with ParentID = 0.
create proc ShowHierarchy
@ParentID int,
@Placement int
as
begin
set nocount on
select @Placement = min(Placement)
from ParticleCMS_Particles
where ParentID = @ParentID and Placement > @Placement
while @Placement is not null
begin
print replicate('-', @@NestLevel * 1) + cast(@Placement as varchar)
exec dbo.ShowHierarchy @ParentID, @Placement
end
end
go
exec ShowHierarchy 0,0 returns
-1
--2
---3
----4
----4
----4
... forever until I cancel the query execution
I tried using:
select top 1 @Placement = Placement
...
order by Placement
which returns
-1
--2
---3
----4
-----4
------4
-------4
--------4
...
until the server kindly stops after 32 nested levels as it is supposed to. (Even though it shouldn't ever run beyond a fifth check.)
Is there something wrong with this logic?
Can it be done with a CTE instead? (I've only made one CTE so far and though it works, I don't have my head wrapped around the concept yet.)
For those who notice that these all share the same ParentID and thus are really on the same hierarchial level, yes, I know. I actually need to be changing levels on the ParentID as it increase but the functionality is identical so ignore that minor detail for now
. -
Hi..
This is an ideal candidate for a CTE. The sample CTEs in Books OnLine are very good; if you need any help getting your head around this please contact me here or on my blog.
Take care..- Ward Pond
blogs.technet.com/wardpond -
The following works....I think the problem is to do with resetting your looping variable. Also nulls in results/variables are very difficult to work with!
CREATE proc ShowHierarchy
@ParentID int,
@inplacement int,
@outplacement int = null output
as
begin
set nocount on
select @outPlacement = min(Placement)
from ParticleCMS_Particles
where ParentID = @ParentID and Placement > @inPlacement
print replicate('-', @@NestLevel * 1) + cast(@outPlacement as varchar)
end
GO
CREATE proc CallShowHierarchy
@ParentID int
as
begin
set nocount on
declare @inplacement integer
declare @outplacement integer
set @outplacement = 0
set @inplacement = @outplacement
while @outPlacement is not null
begin
exec dbo.ShowHierarchy @ParentID, @inplacement, @outPlacement output
set @inplacement = @outplacement
end
end
GO
exec callshowhierarchy @parentid = 0
-
Just simply replace the WHILE clause by IF
Then it works:
create proc ShowHierarchy
@ParentID int,
@Placement int
as
begin
set nocount on
select @Placement = min(Placement)
from ParticleCMS_Particles
where ParentID = @ParentID and Placement > @Placement
IF @Placement is not null
begin
print replicate('-', @@NestLevel * 1) + cast(@Placement as varchar)
exec dbo.ShowHierarchy @ParentID, @Placement
end
end
go
The result of ShowHierarchy 0, 0 is:
-1
--2
---3
----4
Bye
Gabor -
"Just simply replace the WHILE clause by IF"...
so bleeding obvious!...a very good lesson in standing back and keeping it simple
-
Figured it out! The CTE approach eventually led me to what I was trying to do and rather simply at that. I had two problems with my initial attempts. One, I was looking for the data the wrong way and two, CTEs just weren't clicking for me. I've got it now and o is it sweet.
with
SourceRange as (
select ID, Sequence from ParticleCMS_Particles where ParentID in (7)
union all
select P.ID, P.Sequence from ParticleCMS_Particles P
inner join SourceRange S on S.ID = P.ParentID
) Select * from SourceRange
@@NestLevel won't work with CTEs and neither will something easy like a sum(1) since you can't use aggregates in a recursive. I ended up having to record the sequence in the database for other reasons to I took advantage of that to use the CTE. While I don't know what the returned sequence is, the items returned are sequential and I know the starting point so I can simply work from the starting sequence number.
Thanks, Ward. Your confirmation tat a CTE would be right for this made me take the time to finally figure them out.
-
I'm glad this worked for you! FYI, you can also handle the sequencing issues within the syntax of the CTE thusly:
declare
@temp table
(ID int,
ParentID int
)
insert
@temp values (1,2)
insert @temp values (2,3)
insert @temp values (3,4)
insert @temp values (4,5)
insert @temp values (5,6)
insert @temp values (6,7)
insert @temp values (7,8)
;
with SourceRange as (
select ID, 1 AS Sequence from @temp where ParentID in (7)
union all
select P.ID, Sequence + 1 from @temp P
inner join SourceRange S on S.ID = P.ParentID
) Select * from SourceRange
I hope this helps!
Take care..- Ward Pond
blogs.technet.com/wardpond
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply