Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old July 6th, 2006, 11:37 AM
Authorized User
 
Join Date: Jun 2004
Location: , , .
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default recursive query

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





  #2 (permalink)  
Old August 24th, 2006, 09:49 AM
Registered User
 
Join Date: Aug 2006
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I had the same error message and found the following link:

http://www.nigelrivett.net/Triggers_...ers_guide.html

Check it out!
  #3 (permalink)  
Old August 25th, 2006, 05:58 AM
Registered User
 
Join Date: Aug 2006
Location: , , India.
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Your count check is not working simply because you put it in the wrong place. There is no need for two while loops. It should be like this:

Code:
OPEN RetrieveReports

FETCH NEXT FROM RetrieveReports
INTO @Report_ID, @Report_strPlantAreaName, @Report_intPlantAreaID

WHILE (@@FETCH_STATUS = 0) 
BEGIN
    IF @Count > 32
        BREAK
    ELSE
    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
END

Harsh Athalye
India
"Nothing is impossible"


Similar Threads
Thread Thread Starter Forum Replies Last Post
ASP to query a SQL recursive tree chame Classic ASP Basics 4 April 10th, 2012 07:59 PM
Recursive Query for Formatted Output Itech SQL Server 2005 11 June 12th, 2008 04:32 PM
Help with a recursive Query!!! dbayona SQL Server 2005 1 December 20th, 2007 07:40 AM
Recursive Coding arnabghosh Classic ASP Professional 2 July 9th, 2007 02:06 AM
recursive XSLT Help boates XSLT 2 January 11th, 2006 03:50 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.