July 7, 2006 at 4:58 am
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 int
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
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
July 7, 2006 at 5:20 am
July 7, 2006 at 5:34 am
First, the basics: The error is that you have exceeded the nested procedure call level of 32. You have reached 32 levels of recursion for calling GetReports.
I'm not sure what your intention is of this process is. But I suspect you are expecting that the value in @Count will be carried into each recursive call to be able to track how far into the recursion you have gone. In fact, @Count will be local in scope, and a fresh new variable for each recursion. So at each level, it will start at 0. If you want to know what level you are at, use @@NESTLEVEL.
In trying to understand what you attempting to do, I suspect you are trying to find all the parent plants for a particular plantid. If so, I recommend that you look at Joe Celko's book "Trees and Hierarchies in SQL for Smarties" or search for his name for several sample chapters. He has some very good methods on how to tackle this problem with relational databases.
Mark
July 7, 2006 at 6:28 am
This may help. I think I did something similar to what you are trying to do. I think you are just trying to return a table with an exploded listing of all the plantareaid for a given plantarea. Here is a similar function that I write (for property instead of plant) where I did it without recursion or cursors, but using a where loop. This will handle 2 billion levels (theoretically, the limit of the int for the level variable). I modified my code slightly, as I only needed the table of propertyid and not the parent, so I added the parent ID to be returned also.
ALTER
FUNCTION dbo.GetReports(@IncludeParent bit, @intParentPlantAreaID int)RETURNS
@retFindReports TABLE (intParentPlantAreaID int, strPlantAreaName varchar(50), intPlantAreaID int)AS
BEGIN
DECLARE @tmptbl table (parentproperty int, propertyid int, lvl int) DECLARE @m int SET @m = 0 insert @tmptbl values(null, @intParentPlantAreaID, @m) WHILE @@rowcount > 0 BEGIN SET @m = @m + 1 insert @tmptbl select parentproperty, childproperty as propertyid, @m as lvl from propertygroup where parentproperty IN ( SELECT propertyid from @tmptbl WHERE lvl=@m-1) --AND childproperty NOT IN (SELECT propertyID from @tmptbl) if(@m >= 32) BREAK END INSERT @retFindReports SELECT DISTINCT parentproperty, '', propertyid FROM @tmptbl; RETURNEND
Hope this helps
Mark
July 7, 2006 at 6:53 am
Thanks so much for all the replies! I got the recursion working for my originla but can seem to get it to recall for areas that are down deep in the level so I will try your piece of code Mark.
Thanks!
cathie
July 7, 2006 at 7:00 am
CREATE FUNCTION dbo.GetReports(@IncludeParent bit, @intParentPlantAreaID int)
RETURNS @retFindReports TABLE (intParentPlantAreaID int, strPlantAreaName varchar(50), intPlantAreaID int)
AS
BEGIN
DECLARE @child TABLE (intParentPlantAreaID int, strPlantAreaName varchar(50), intPlantAreaID int)
DECLARE @parent TABLE (intParentPlantAreaID int, strPlantAreaName varchar(50), intPlantAreaID int)
INSERT INTO @child
SELECT intParentPlantAreaID, strPlantAreaName , intPlantAreaID
FROM tblSafeTrackIT_Config_Area
WHERE intParentPlantAreaID=-@intParentPlantAreaID
WHILE EXISTS(SELECT * FROM @child)
BEGIN
DELETE FROM @parent
INSERT INTO @parent
SELECT p.intParentPlantAreaID, p.strPlantAreaName , p.intPlantAreaID
FROM @child c
INNER JOIN tblSafeTrackIT_Config_Area p
ON p.intPlantAreaID = c.intParentPlantAreaID
INSERT INTO @retFindReports
SELECT intParentPlantAreaID, strPlantAreaName , intPlantAreaID
FROM @child
DELETE FROM @child
INSERT INTO @child
SELECT intParentPlantAreaID, strPlantAreaName, intPlantAreaID
FROM @parent
END
RETURN
END
Far away is close at hand in the images of elsewhere.
Anon.
July 7, 2006 at 7:58 am
Thanks again for all your help!
Each parent can have children but these children nodes can also have children themselves etc
So what I need to do is use the plantareaid of each child to check if there are any entries that have that as their parent id
I have implemeted your solution Mark but this still doesnt returen children of children nodes but does return the children with the parentplantarea id that i pass to the function but i still need to check records to see if these child nodes themselves have children have a parentplantareaid that is the plantareaid of them.
Europe
France
Ireland
Dublin
City
County
England
So that for Ireland Dublin is returned but so are its childre City and County and if these had children then they would be returned too.
Thanks,
Cathie
July 7, 2006 at 9:41 am
ok guys - here we go - i modified yours a small bit mark and i got it to return subareas of subareas etc
CREATE FUNCTION dbo.GetReports3(@IncludeParent bit, @intParentPlantAreaID int)
RETURNS @retFindReports
TABLE (intParentPlantAreaID int, strPlantAreaName varchar(255), intPlantAreaID int )
AS
BEGIN
DECLARE @tmptbl table (parentproperty int, propertyid int, lvl int, strPlantAreaName varchar(255))
DECLARE @m int,@strPlantAreaName varchar(255)
SET @m = 0
SET @strPlantAreaName = (select strPlantAreaname from tblSafeTrackIT_Config_Area where intPLantAreaID = @intParentPlantAreaID)
insert @tmptbl
values(null, @intParentPlantAreaID, @m,@strPlantAreaName) WHILE @@rowcount > 0
BEGIN
insert @tmptbl
select intParentPLantAreaID, intPLantAreaID as propertyid, @m as lvl , strPLantAreaName
from tblSafeTrackIT_Config_Area
where intParentPLantAreaID IN ( SELECT propertyid from @tmptbl WHERE lvl=@m-1)
--AND intParentPLantAreaID NOT IN (SELECT propertyID from @tmptbl)
--if(@m >= 32)
--BREAK
END INSERT @retFindReports
SELECT distinct parentproperty, strPlantAreaName, propertyid FROM @tmptbl;
RETURN
END
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply