none
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

    Question

  • Hi,

     

    I'm getting Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).  messge when I try to execute below queries.

     

    dt_GetFullPaths is used inside dt_GetWrongPoints.

    dt_GetWrongPoints is used inside TestImExportRefTable.

    And some cusors were used inside these stored procedures.

     

    ------- To get the fullpath of objects

    if ( exists ( select * from sysobjects where name = 'dt_GetFullPaths' ) )

    DROP PROCEDURE dt_GetFullPaths

    GO

    create procedure dt_GetFullPaths(@PointIdHi int, @PointIdLo int)

    as

    BEGIN

     

    .......................

     

     declare ImExportRefList_CURSOR cursor for

    select ObjectIdHi, ObjectIdLo from AlarmInfo

     

    open ImExportRefList_CURSOR

    fetch next from ImExportRefList_CURSOR

    into @ObjIdHi, @ObjIdLo

     

    while @@FETCH_STATUS = 0

    begin

     

    EXEC dt_GetFullPath @ObjIdHi, @ObjIdLo, @ObjName OUTPUT

     

    fetch next from ImExportRefList_CURSOR into @ObjIdHi, @ObjIdLo

    end

     

    close ImExportRefList_CURSOR

    deallocate ImExportRefList_CURSOR

     

    END

    GO

    ------------- end of fullpaths

     

    ---- To find the Wrong Points

    if ( exists ( select * from sysobjects where name = 'dt_GetWrongPoints' ) )

    DROP PROCEDURE dt_GetWrongPoints

    GO

    create procedure dt_GetWrongPoints(@SiteHi int, @SiteLo int,@NetAddress int)

    as

     

    ....................

     

    open Network_cursor

    fetch next from Network_cursor into @NetworkObjectIdHi, @NetworkObjectIdLo

     

    WHILE @@FETCH_STATUS = 0

    begin

     

    EXEC dt_BuildOMSIDFromIENAD @NetAddress, @NetworkObjectIdHi, @NetworkObjectIdLo, @PointIdHi output, @PointIdLo output

     

    EXEC dt_GetFullPaths @PointIdHi, @PointIdLo

     

    fetch next from Network_cursor into @NetworkObjectIdHi, @NetworkObjectIdLo

    end

    --select ObjectIdLo,NetAddress from ImExportRefOutput where ObjectIdHi = 0

    close Network_cursor

    deallocate Network_cursor

    GO

    ------------- end of wrong points

     

    if ( exists ( select * from sysobjects where name = 'TestImExportRefTable' ) )

    DROP PROCEDURE TestImExportRefTable

    GO

    create Procedure TestImExportRefTable

    AS

    .......................

    declare ImExportRefList cursor for

    select NetAddress, SiteHi, SiteLo,ObjectIdHi, ObjectIdLo,AlarmFlag,ImportFlag,InfExportMask from ImExportRef

    open ImExportRefList

    fetch next from ImExportRefList

    into @NetAddress , @SiteHi , @SiteLo ,@CxIdHi , @CxIdLo, @AlarmFlag ,

    @ImportFlag , @InfExportMask

    while @@FETCH_STATUS = 0

    begin

    exec dt_BuildOMSIDFromIENAD @NetAddress, @SiteHi, @SiteLo, @ObjIdHi output, @ObjIdLo output

     

    ........................

     

    fetch next from ImExportRefList into @NetAddress , @SiteHi , @SiteLo ,@CxIdHi , @CxIdLo, @AlarmFlag ,

    @ImportFlag , @InfExportMask

    end

    close ImExportRefList

    deallocate ImExportRefList

     

    declare ImExportRefOutput_Results cursor for select

    SiteIdHi ,

    SiteIdLo ,

    NetAddress

    from ImExportRefOutput WHERE ObjectIdHi = 0

     

    open ImExportRefOutput_Results

     

    fetch next from ImExportRefOutput_Results into @SiteHiResults , @SiteLoResults, @NetAddressResults

    while @@FETCH_STATUS = 0

    BEGIN

    exec dt_GetWrongPoints @SiteHiResults , @SiteLoResults, @NetAddressResults

    END

    close ImExportRefOutput_Results

    deallocate ImExportRefOutput_Results

     

    EXEC TestImExportRefTable

     

    When I execute TestImExportRefTable I'm getting this error.

    Is there anything wrong with the nesting?

     

    Msg 217, Level 16, State 1, Procedure TestImExportRefTable, Line 41

    Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

     

    Can any one help me?

     

    Thanks,

    Sindhu

    Thursday, November 06, 2008 10:01 PM

Answers

  • I'd recommend that you stop, and look at how you are writing your code.  Nested cursors like this are horrific for performance.  This generally indicates a lack of understand of a particular problem or the coding strengths of set based operations.  I can't make heads or tails of what specifically you are intending to do in your code because all I see are cursors fetching into SP calls, and those stored procedures, only do an additional fetch into another cursor that calls another stored procedure.

     

    If you can provide more information, maybe we can guide you towards an appropriate set based solution to your problem.

    Friday, November 07, 2008 4:48 PM
    Moderator

All replies

  • Without going through all of your code, it looks like the nesting level error is coming from the fact that dt_GetFullPaths is calling itself - is this your intent?

    If so, is there no other way to fetch this info than via (deep) recursion?

     

    Friday, November 07, 2008 5:43 AM
    Moderator
  • I'd recommend that you stop, and look at how you are writing your code.  Nested cursors like this are horrific for performance.  This generally indicates a lack of understand of a particular problem or the coding strengths of set based operations.  I can't make heads or tails of what specifically you are intending to do in your code because all I see are cursors fetching into SP calls, and those stored procedures, only do an additional fetch into another cursor that calls another stored procedure.

     

    If you can provide more information, maybe we can guide you towards an appropriate set based solution to your problem.

    Friday, November 07, 2008 4:48 PM
    Moderator