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