recursive function

[cathiec]

hi all,

i have the following recursive function but i keep on getting the error

Server: Msg 217, Level 16, State 1, Procedure GetReports, Line 31
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

I have tried putting in a count on this but i still get the error.

Here is the function. Would appreciate any suggestions!

CREATE FUNCTION dbo.GetReports(@IncludeParent bit, @intParentPlantAreaID int)
RETURNS @retFindReports TABLE (intParentPlantAreaID int, strPlantAreaName varchar(50), intPlantAreaID int)
AS
BEGIN
IF (@IncludeParent=1)
BEGIN
INSERT INTO @retFindReports
SELECT intParentPlantAreaID, strPlantAreaName , intPlantAreaID FROM tblSafeTrackIT_Config_Area WHERE intParentPlantAreaID=-@intParentPlantAreaID
END

DECLARE @Report_ID int, @Report_strPlantAreaName varchar(50), @Report_intPlantAreaID int, @Count varchar(2)

SET @Count = '1'


DECLARE RetrieveReports CURSOR STATIC LOCAL FOR

SELECT intParentPlantAreaID, strPlantAreaName , intPlantAreaID FROM tblSafeTrackIT_Config_Area WHERE intPlantAreaID=@intParentPlantAreaID

OPEN RetrieveReports

FETCH NEXT FROM RetrieveReports
INTO @Report_ID, @Report_strPlantAreaName, @Report_intPlantAreaID
WHILE @Count <= 32


WHILE (@@FETCH_STATUS = 0)

BEGIN

INSERT INTO @retFindReports
SELECT * FROM dbo.GetReports(0,@Report_ID)

INSERT INTO @retFindReports
VALUES(@Report_ID,@Report_strPlantAreaName, @Report_intPlantAreaID)

FETCH NEXT FROM RetrieveReports
INTO @Report_ID, @Report_strPlantAreaName, @Report_intPlantAreaID
SET @Count = @Count + 1

END




CLOSE RetrieveReports
DEALLOCATE RetrieveReports

RETURN
END

 

[JayKusch]

Try changing the code from :

WHILE @Count <= 32

To ...

WHILE @Count < 32

Thanks

J. Kusch

 

[cathiec]

Thanks for your reply J but unfortunatley I am still struggling with this!!

i found the line that it is falling over on

SELECT * FROM dbo.GetReports(0,@Report_ID)

if i enter a number less than 32 for @Report_ID then the query runs but returns no rows as there are no records with a intParentPlantAreaID of those numbers.

But if i enter a valid parameter then i get the error mentioned before!

Would appreciate any suggestions on this one

Thanks,
Cathie

 

[MichaelRed]

I am sure that you must be familiar with recursion, so hesitate to offer any thoughts. On the other hand, it seems obvious that recursive functions always build "stacks" of the procedure, including the local variables and argumennts. In any / every instantaion you need an limit condition to escape ont of the increasing levels of the 'stack'. In your routine, this is the Count var. The fact that this is initalized as 1 "1", and the error refers to the value as "0", suggests that the routine is -in fact- executing properly down to the level desired, and returning through the stack and failing at the return to the top most level and attempte to return to the next most top level -which doesnt exist. To see if this is the case, have the routine return a local var which you can observe. Watch it. If it counts up to the limit qand back down to "1", the answer would be to include another escape clause to stop the process when the count rechaes zero.





MichaelRed