nesting limit exceeded...but I don't understand why.

I have written a recursive function that generates an XML hierarchy. I have gone through the data being selected and I have verified that the hierarchy is only 19 levels at its deepest. (there are several thousand records involved, and 1 level may have several hundred records). But, I am receiving this error:
"Msg 217, Level 16, State 1, Line 1
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)."

Here is the function I have written:

ALTER FUNCTION [dbo].[fn_WPMTREE](@.SceneID int)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT
BEGIN
RETURN
(SELECT s.ID As "@.id",
s.TITLE as "@.title",
s.CLASS_ID as "@.clsID",
CASE WHEN s.PARENT_ID=@.SceneID
THEN dbo.fn_WPMTREE(s.ID)
END
FROM SCENE s
WHERE s.PARENT_ID = @.SceneID
FOR XML PATH('Scene'), TYPE)
END

Does anyone have any suggestions as to why I may be getting this error, or how to debug for it or work around it?

Thank you for any advice you can give.

Do you have any records in which ID = PARENT_ID?

Run SELECT ID, PARENT_ID FROM SCENE WHERE ID = PARENT_ID and see.

If so, you might want to consider adding a check constraint to the table that would forbid such a circumstance. Another alternative might be to exit the function if the function detects ID = PARENT_ID.

|||

When I mockup with:

create table dbo.scene
( id integer,
parent_id integer,
class_id integer,
title varchar(20)
)
go

insert into dbo.scene values (1, null, 1, 'This is a test')
insert into dbo.scene values (2, 1, 1, 'Record #2')
insert into dbo.scene values (3, 2, 1, 'Record #3')

and run:

select dbo.fn_WPMTREE (1) as [the Scene]

I get:

-- the Scene
-- -
-- <Scene id="2" title="Record #2" clsID="1"><Scene id="3" title="Record #3" clsID="1" /></Scene>

Is this what you expect?

|||Absolutely brilliant.

I had a scene that had a PARENT_ID = ID. And, that caused the infinite loop. I thought it was a loop being created somewhere, but I thought it was of the type Scene1.Parent_ID = Scene2.ID; Scene2.Parent_ID = Scene1.ID.
But, it was even more direct than that.

Thanks so much!
Sincerely.
roger

nested loop function

I want to know how to create a recursive loop/function in SQL, I can't seem to figure out how to do it.

The database table I am working with is simply the following:

SeedID, ThisParentSeedID

1, 0

2, 1

3, 1

4, 2

5, 4

6, 5

7, 6

8, 7

9, 7

10, 7

11, 10

12, 0

13, 0

14, 0

The example table above shows that SeedID 1 = the parent level of the data. SeedID 2 and 3 are children of SeedID 1, 4 is child of 2, 5 is child of 4... 12 13 and 14 are also parent levels (they are not children of anything).

I want to know how to create a SQL script that is "object oriented" in that I will not have to create as many levels of nested scripts as there are nested "children" in the data.

What I am wanting to figure out is, with a single script, "which sub-children are assigned to [@.SeedID]"? So if this script was called, and @.SeedID = 1, it would return (2,3,4,5,6,7,8,9,10,11). If @.SeedID = 12, it would return null. If @.SeedID = 7, it would return (8,9,10,11)

I have tried to keep my question and data as simple as possible for the sake of getting some feedback or help. If you want me to clarify or explain better, please ask me to!

CTE (Common Table expression) is best suited for your needs. Here you go

Declare @.ID as INT
SET @.ID = 7

;WITH myCTE AS
(
SELECT SeedID, ParentID FROM Seeds where SeedID = @.ID
UNION ALL
SELECT Seeds.SeedID, Seeds.ParentID From Seeds INNER JOIN myCte
ON Seeds.ParentID = myCte.SeedID
)
SELECT SeedID FROM myCTE where SeedID <> @.ID

|||

--Create the functionCREATE FUNCTION dbo.udf_GetChildren (@.parentIdint )RETURNSVarchar(100)ASBEGINDECLARE @.Childvarchar(100)SELECT @.Child =coalesce(@.child,'') + (casewhen @.childisnot nullthen','else''end ) +convert(varchar,SeedId)FROM YourTable CWHERE C.ThisParentSeedID = @.parentIdSELECT @.Child = @.Child + dbo.udf_GetChildren(seedid)FROM YourTable CWHERE C.ThisParentSeedID = @.parentIdReturnCoalesce(@.child,'')END--Call the functionSELECT dbo.udf_GetChildren(1)