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

  • Question

  • I am seeking to delete data on database with complex foreign key relationships.  The script I'm using, from mssqltips, a script that works on the parent tables with a small number of child tables.  I encounter the 'Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).' error on the larger parent tables with a large number of child tables.  How can I workaround this error.  The script to get the foreign keys is as follows:

    if object_id('dbo.SearchForeignKeys', 'P') is not null
    drop proc dbo.SearchForeignKeys;
    go
    create proc dbo.SearchForeignKeys 
      @table varchar(256)
    , @lvl int=0 
    , @ParentTable varchar(256)='' 
    , @debug bit = 1
    as
    begin
    set nocount on;
    declare @dbg bit;
    set @dbg=@debug;
    if object_id('tempdb..#tbl', 'U') is null
    create table  #tbl  (id int identity, tablename varchar(256), lvl int, ParentTable varchar(256));
    declare @curS cursor;
    if @lvl = 0
    insert into #tbl (tablename, lvl, ParentTable)
    select @table, @lvl, Null;
    else
    insert into #tbl (tablename, lvl, ParentTable)
    select @table, @lvl,@ParentTable;
    if @dbg=1
    print replicate('----', @lvl) + 'lvl ' + cast(@lvl as varchar(10)) + ' = ' + @table;

    if not exists (select * from sys.foreign_keys where referenced_object_id = object_id(@table))
    return;
    else
    begin -- else
    set @ParentTable = @table;
    set @curS = cursor for
    select tablename=object_schema_name(parent_object_id)+'.'+object_name(parent_object_id)
    from sys.foreign_keys 
    where referenced_object_id = object_id(@table)
    and parent_object_id <> referenced_object_id; -- add this to prevent self-referencing which can create a indefinitive loop;

    open @curS;
    fetch next from @curS into @table;

    while @@fetch_status = 0
    begin --while
    set @lvl = @lvl+1;
    -- recursive call
    exec dbo.SearchForeignKeys @table, @lvl, @ParentTable, @dbg;
    set @lvl = @lvl-1;
    fetch next from @curS into @table;
    end --while

    close @curS;
    deallocate @curS;
    end -- else
    if @lvl = 0
    select * from #tbl  ;
    return;
    end

    go

    -------------------------------------------------------------------------------------------------------------------------------------------

    The script to generate the delete statements is as follows:

    if object_id('tempdb..#tmp') is not null
    drop table #tmp;
    create table  #tmp  (id int, tablename varchar(256), lvl int, ParentTable varchar(256));


    insert into #tmp 
    exec dbo.SearchForeignKeys @table='dbo.MyTable', @debug=0;

    declare @where varchar(max) ='where MyTable.ID = 1' -- if @where clause is null or empty, it will delete tables as a whole with the right order
    declare @curFK cursor, @fk_object_id int;
    declare @sqlcmd varchar(max)='', @crlf char(2)=char(0x0d)+char(0x0a);
    declare @child varchar(256), @parent varchar(256), @lvl int, @id int;
    declare @i int;
    declare @t table (tablename varchar(128));
    declare @curT cursor;
    if isnull(@where, '')= ''
    begin
    set @curT = cursor for select tablename, lvl from #tmp order by lvl desc
    open @curT;
    fetch next from @curT into @child, @lvl;
    while @@fetch_status = 0
    begin -- loop @curT
    if not exists (select 1 from @t where tablename=@child)
    insert into @t (tablename) values (@child);
    fetch next from @curT into @child, @lvl;
    end -- loop @curT
    close @curT;
    deallocate @curT;

    select  @sqlcmd = @sqlcmd + 'delete from ' + tablename + @crlf from @t ;
    print @sqlcmd;
    end
    else
    begin 
    declare curT cursor for
    select  lvl, id
    from #tmp
    order by lvl desc;

    open curT;
    fetch next from curT into  @lvl, @id;
    while @@FETCH_STATUS =0
    begin
    set @i=0;
    if @lvl =0
    begin -- this is the root level
    select @sqlcmd = 'delete from ' + tablename from #tmp where id = @id;
    end -- this is the roolt level

    while @i < @lvl
    begin -- while

    select top 1 @child=TableName, @parent=ParentTable from #tmp where id <= @id-@i and lvl <= @lvl-@i order by lvl desc, id desc;
    set @curFK = cursor for
    select object_id from sys.foreign_keys 
    where parent_object_id = object_id(@child)
    and referenced_object_id = object_id(@parent)

    open @curFK;
    fetch next from @curFk into @fk_object_id
    while @@fetch_status =0
    begin -- @curFK

    if @i=0
    set @sqlcmd = 'delete from ' + @child + @crlf +
    'from ' + @child + @crlf + 'inner join ' + @parent  ;
    else
    set @sqlcmd = @sqlcmd + @crlf + 'inner join ' + @parent ;

    ;with c as 
    (
    select child = object_schema_name(fc.parent_object_id)+'.' + object_name(fc.parent_object_id), child_col=c.name
    , parent = object_schema_name(fc.referenced_object_id)+'.' + object_name(fc.referenced_object_id), parent_col=c2.name
    , rnk = row_number() over (order by (select null))
    from sys.foreign_key_columns fc
    inner join sys.columns c
    on fc.parent_column_id = c.column_id
    and fc.parent_object_id = c.object_id
    inner join sys.columns c2
    on fc.referenced_column_id = c2.column_id
    and fc.referenced_object_id = c2.object_id
    where fc.constraint_object_id=@fk_object_id
    )
    select @sqlcmd =@sqlcmd +  case rnk when 1 then ' on '  else ' and ' end 
    + @child +'.'+ child_col +'='  +  @parent   +'.' + parent_col
    from c;
    fetch next from @curFK into @fk_object_id;
    end --@curFK
    close @curFK;
    deallocate @curFK;
    set @i = @i +1;
    end --while
    print @sqlcmd + @crlf + @where + ';';
    print '';
    fetch next from curT into  @lvl, @id;
    end
    close curT;
    deallocate curT;
    end

    ----------------------------------------------------------------------------------------------------------------------------------------


    Friday, June 14, 2019 2:52 PM

Answers

  • If you get that error, this would indicate one of:

    1) You have dependencies that is more than 32 levels deep.
    2) There are circular references. Say that table A has a an FK to B, which has an FK to C, which has an FK to A.

    I would hold the latter as the most likely. To avoid the problem, you would need to check whether a table already is present in the temp table before making the recursive call.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by Circle1One Monday, June 17, 2019 5:15 PM
    Friday, June 14, 2019 9:30 PM

All replies

  • If you get that error, this would indicate one of:

    1) You have dependencies that is more than 32 levels deep.
    2) There are circular references. Say that table A has a an FK to B, which has an FK to C, which has an FK to A.

    I would hold the latter as the most likely. To avoid the problem, you would need to check whether a table already is present in the temp table before making the recursive call.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by Circle1One Monday, June 17, 2019 5:15 PM
    Friday, June 14, 2019 9:30 PM
  • Hi Circle1One,

     

    Thank for your issue.

     

    The error means you're calling a procedure, view or function from your procedure, and then from that proc calling another, etc 32 levels deep. Or it could be recursive with the proc calling itself.

     

    Your procedure is created with an 'exec dbo.SearchForeignKeys' inside it. Therefore, a GO must be placed before the EXEC so the procedure will be Created/Altered before getting executed. Thus, avoiding the RECURSION.

     

    Either way, you're going to have to track down where that is coming from and change the code of your function so that the nesting level isn't anywhere close to that deep. There's no way to change the maximum nesting level.

     

    Please check it.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, June 17, 2019 6:14 AM