CTE Infinite Recursion

[tshad]

I have a situation where you can have an infinite recusion.

There is an issue where editing a page could cause infinite recursion. This
would happen if a distribution folder were put into itself. It would also
happen if you put folder "A" into folder "B" where folder "B" is in any of
the folders contained in any of the folders found in folder "A". A little
convoluted sentence but that is the problem.

A is in B and B is in C

If you put A into C then C will eventually point back to itself and you the
recursion will continue on

YOu have to have a way to say that you have already seen this folder before
so ignore it.

Here is an example:

DECLARE @MultipleAnchors TABLE(
ParentID int NULL,
ID int NOT NULL );

INSERT INTO @MultipleAnchors
VALUES
(NULL, 1),
(NULL, 2),
(1, 3),
(3, 1), // Causes the infinite recursion
(1, 4),
(4, 5),
(2, 6),
(6, 7),
(7, 8);

WITH MultipleAnchorRowsCTE
AS
(
SELECT ParentID, ID, Level = 0
FROM @MultipleAnchors
WHERE ParentID IS NULL
UNION ALL
SELECT children.ParentID, children.ID, Level + 1
FROM @MultipleAnchors children
JOIN MultipleAnchorRowsCTE macte ON macte.ID = children.ParentID
)
SELECT ParentID, ID, Level
FROM MultipleAnchorRowsCTE;

Is there a way to handle this in the CTE?

Thanks,

Tom

 

[Mike Lovell]

I have a situation where you can have an infinite recusion.

There is an issue where editing a page could cause infinite recursion.
This would happen if a distribution folder were put into itself. It would
also happen if you put folder "A" into folder "B" where folder "B" is in
any of the folders contained in any of the folders found in folder "A".
A little convoluted sentence but that is the problem.

A is in B and B is in C

If you put A into C then C will eventually point back to itself and you
the recursion will continue on

YOu have to have a way to say that you have already seen this folder
before so ignore it.

Here is an example:

DECLARE @MultipleAnchors TABLE(
ParentID int NULL,
ID int NOT NULL );

INSERT INTO @MultipleAnchors
VALUES
(NULL, 1),
(NULL, 2),
(1, 3),
(3, 1), // Causes the infinite recursion
(1, 4),
(4, 5),
(2, 6),
(6, 7),
(7, 8);

WITH MultipleAnchorRowsCTE
AS
(
SELECT ParentID, ID, Level = 0
FROM @MultipleAnchors
WHERE ParentID IS NULL
UNION ALL
SELECT children.ParentID, children.ID, Level + 1
FROM @MultipleAnchors children
JOIN MultipleAnchorRowsCTE macte ON macte.ID = children.ParentID
)
SELECT ParentID, ID, Level
FROM MultipleAnchorRowsCTE;

Is there a way to handle this in the CTE?

I think you might be in the wrong newsgroup for that question.

 

[tshad]

You're right.

Didn't realize which one I was in.

Thanks,

Tom