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

    Question

  • I'm unsure as to why I'm getting this problem.  I understand the nature of the error message and the searching I've done indicates that I'm unable to change the depth of this.  So my presumption is that the problem is in how this/these procedure 's/are being called.

    I have two (at the moment) procedures.  They are very similar in nature, the difference is that the Club Procedure looks for person.  I say 'at the moment' because I'm just starting out with this project and will have more that it will need to parse out in the future

    The input XML (@in in this case) in my Test Script looks like this: 

    <Root>
    
     <Person ID="1">
    
     <Membership />
    
     <Club ID="1">
    
      <Address />
    
     </Club>		
    
     </Person>
    
    </Root>
    
    

     

    The procedure for this is:

     

    PROCEDURE [dbo].[spGetPersonData] (
    
     @in XML,
    
     @ret XML OUTPUT
    
    )
    
    AS
    
    BEGIN
    
     SET NOCOUNT ON;
    
     DECLARE @ChildEntities XML, @node VARCHAR(50), @eType INT, @id INT, @Ctr INT, @club XML
    
     DECLARE @address BIT = 0, @email BIT = 0, @membership BIT = 0, @officers BIT = 0, @phone BIT = 0, @web BIT = 0 
    
     SET @Ctr = 1	
    
     SELECT @node = A.a.value( 'local-name(.)', 'VARCHAR(50)' )
    
     FROM @in.nodes( '*/*' ) A(a)
    
    
    
     SELECT @id = A.b.value( 'data(.)', 'INT' )
    
     FROM @in.nodes( '*/*' ) B(a)
    
     CROSS APPLY B.a.nodes( './@*' ) A(b)
    
    	
    
     SELECT @ChildEntities = C.a.query( 'Club' )
    
     FROM @in.nodes( '*/*' ) C(a)
    
     SET @ChildEntities = ( SELECT @ChildEntities
    
    		 FOR XML RAW( 'ICE' ) );
    
    		
    
     IF @ChildEntities IS NOT NULL 
    
     OR LEN ( CAST( @ChildEntities AS VARCHAR(MAX) ) ) > 0 
    
     OR CAST( @ChildEntities AS VARCHAR(MAX) ) != ''	 
    
     BEGIN			
    
      EXEC [dbo].[spGetClubData] @ChildEntities, @club OUTPUT
    
     END
    
    
    
     DECLARE @ChildNodes XML, @CurrentNode VARCHAR(50), @t XML
    
     SELECT @ChildNodes = D.a.query( '*' )
    
     FROM @in.nodes( '//*[local-name()=sql:variable( "@node" )]' ) D(a)
    
    
    
     SELECT @CurrentNode = E.a.value( 'local-name(.)', 'VARCHAR(50)' )
    
     FROM @ChildNodes.nodes( '/*[position()=sql:variable( "@Ctr" )]' ) E(a)
    
     WHILE @Ctr <= CAST( CAST( @in.query( 'count( //*[local-name()=sql:variable( "@node" )]/* )' ) AS VARCHAR(5) ) AS INT )
    
     BEGIN			
    
      IF @CurrentNode = 'Address'
    
      SET @address = 1
    
      IF @CurrentNode = 'Email'
    
      SET @email = 1
    
      IF @CurrentNode = 'Membership'
    
      SET @membership = 1
    
      IF @CurrentNode = 'Officers'
    
      SET @officers = 1
    
      IF @CurrentNode = 'Phone'
    
      SET @phone = 1
    
      IF @CurrentNode = 'WebAddress'
    
      SET @web = 1
    
      SET @Ctr += 1	
    
     END
    
    
    
     DECLARE @cmd NVARCHAR(MAX)
    
     SET @cmd = [dbo].[fncGetCommand]( @id, @etype, @address, @email, @membership, @officers, @phone, @web )	
    
     SET @cmd += ', @club FROM [entity.Person] WHERE ID = ' + CAST( @id AS NVARCHAR(15) ) + ' FOR XML RAW( ''Person'' ) )'
    
    		
    
     EXEC sp_executesql @cmd, N'@club XML, @ret XML OUTPUT', @club, @ret OUTPUT
    
     SELECT @ret	
    
    	
    
    END
    
    

    The error seems to come from this line:

    SELECT @ChildEntities = C.a.query( 'Club' )
    FROM @in.nodes( '*/*' ) C(a)
    SET @ChildEntities = ( SELECT @ChildEntities
    		 FOR XML RAW( 'ICE' ) );
    

    Any help or pointers you gurus may be able to provide would be appreciated.

    • Edited by Stig1031 Tuesday, January 25, 2011 10:08 PM Forgot to note which line was reported as throwing the error
    Tuesday, January 25, 2011 10:05 PM

Answers

  • Do you mean "A stored procedure in the CLR" as you have written here?  Or did you mean "Do the XML ballet that's in the stored procedure in the CLR?"

    I mean that rather than writing a stored procdure in T-SQL using XQuery, write a stored procedure in C# (or VB.Net) and use the XML classes in .Net.

    If you meant it the first way, I'd be more than happy to do the SPROC somewhere besides where it, but T-SQL isn't my first love,

    T-SQL is my prime language as well, but just because we have a good hammer, that does not mean that we should use it to push screws.

    I can't assist with how to use the XML classes in .Net, because I have never used them. XQuery in T-SQL is certainly powerful to some extent, but there are also lots of limitations. Not the least can performance be very poor.

     I also eliminated some function calls that were in the 'GetCommand' function, and added those SELECT statements to my IF Statements within the loop.  Which brings up the question:  Is each function call part of this nest level limitation? 

    Each invocation you make of a new module or batch of dynamic SQL increases @@nestlevel with one. sp_executesql increases @@nestlevel with 2 since you first invoke sp_executesql and the then batch of dynamic SQL. When you exit a scope, @@nestlevel is decreased.

    While you can write recursive functions and procedures in T-SQL, it is not designed with the intention that recursion should be a commonly used feature. I don't know exactly what you are up to, since you did not post all the code, and you did not really describe the underlying problem. But you may be stretching the limits of what T-SQL is intended for, whence my suggestion to use C#.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    • Marked as answer by Ai-hua Qiu Wednesday, February 02, 2011 7:05 AM
    Wednesday, January 26, 2011 9:02 AM

All replies

  • The error seems to come from this line:

    [sql]
    SELECT @ChildEntities = C.a.query( 'Club' )
    FROM @in.nodes( '*/*' ) C(a)
    SET @ChildEntities = ( SELECT @ChildEntities
               FOR XML RAW( 'ICE' ) );


    No, the error comes from one of the lines where you invoke EXEC. Since you did not include all the code, it is not possible run your procedure to debug it.

    I would suggest that on the entrance of the procedure add a SELECT of @@nestlevel and the input parameter. Also the same SELECT to the dynamic SQL you are creating. This will help to see whether you are actually pushing the limits of SQL Server, you are losing yourself in infinite recursion.

    Maybe you should consider doing all this XML ballet in a stored procedure in the CLR instead?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    Tuesday, January 25, 2011 11:01 PM
  • Maybe you should consider doing all this XML ballet in a stored procedure in the CLR instead?

    Sorry, I don't quite get this:

    Do you mean "A stored procedure in the CLR" as you have written here?  Or did you mean "Do the XML ballet that's in the stored procedure in the CLR?"

    If you meant it the first way, I'd be more than happy to do the SPROC somewhere besides where it, but T-SQL isn't my first love, so I'd need a clue as to what you meant.  If you meant it the second way, it's not really an option to do it in the CLR because I'd still have to parse and insert/select/etc within the procedures.

     Either way, your previous post made me take another, more thorough look at what I'd written.  I think the problem was this: Which I changed to

     SELECT @ChildEntities = C.a.query( 'Club' )
     FROM @in.nodes( '*/*' ) C(a)
     SET @ChildEntities = ( SELECT @ChildEntities
    		 FOR XML RAW( 'ICE' ) );
     IF @ChildEntities IS NOT NULL 
     OR LEN ( CAST( @ChildEntities AS VARCHAR(MAX) ) ) > 0 
     OR CAST( @ChildEntities AS VARCHAR(MAX) ) != ''	 
     BEGIN			
     EXEC [dbo].[spGetClubData] @ChildEntities, @club OUTPUT
     END
    
    


    Which I changed to:

    SELECT @ChildEntities = C.a.query( 'Club' )
     FROM @in.nodes( '*/*' ) C(a)
     IF @ChildEntities IS NOT NULL 
     OR LEN ( CAST( @ChildEntities AS VARCHAR(MAX) ) ) > 0 
     OR CAST( @ChildEntities AS VARCHAR(MAX) ) != ''	 
     BEGIN
     SET @ChildEntities = ( SELECT @ChildEntities
    		 FOR XML RAW( 'ICE' ) );			
     EXEC [dbo].[spGetClubData] @ChildEntities, @club OUTPUT
     END
    
    

     

     I also eliminated some function calls that were in the 'GetCommand' function, and added those SELECT statements to my IF Statements within the loop.  Which brings up the question:  Is each function call part of this nest level limitation? 

    For instance if I have the following, does that count as 4 levels of nesting? 

    SELECT *, [dbo].[fncOne](@param), [dbo].[fncTwo](@param),[dbo].[fncThree](@param),[dbo].[fncFour](@param)
    

     

     

    Tuesday, January 25, 2011 11:30 PM
  • To answer your last question only - if these functions are non-recursive, it will not be 4 levels of nesting. However, the less scalar-valued functions you use, the better for performance. Always try to use inline table valued functions instead of scalar-valued if possible.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, January 25, 2011 11:34 PM
  • To answer your last question only - if these functions are non-recursive, it will not be 4 levels of nesting. However, the less scalar-valued functions you use, the better for performance. Always try to use inline table valued functions instead of scalar-valued if possible.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog


    Do you have a quick explanation of the difference?  I had a lot to do to get to the point where I got the above errors, but this is one of those things that I jotted down to look up.  The functions in my example were based on a function selecting the data from the table and returning an XML type. 

    More importantly, what I'm hearing, then, is that calling a function that's non-recursive doesn't add to the depth of the nesting level.  So presuming the functions themselves aren't recursive in nature, I should be able to call as many functions in a SPROC as I like?

    Thanks for the help.

    Tuesday, January 25, 2011 11:41 PM
  • For the scalar function performance discussion take a look at the following quiz. If you're not calling your SP in a loop and the functions are non-recursive, I would assume you can call as many as you want in one SP. 
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, January 26, 2011 12:10 AM
  • Do you mean "A stored procedure in the CLR" as you have written here?  Or did you mean "Do the XML ballet that's in the stored procedure in the CLR?"

    I mean that rather than writing a stored procdure in T-SQL using XQuery, write a stored procedure in C# (or VB.Net) and use the XML classes in .Net.

    If you meant it the first way, I'd be more than happy to do the SPROC somewhere besides where it, but T-SQL isn't my first love,

    T-SQL is my prime language as well, but just because we have a good hammer, that does not mean that we should use it to push screws.

    I can't assist with how to use the XML classes in .Net, because I have never used them. XQuery in T-SQL is certainly powerful to some extent, but there are also lots of limitations. Not the least can performance be very poor.

     I also eliminated some function calls that were in the 'GetCommand' function, and added those SELECT statements to my IF Statements within the loop.  Which brings up the question:  Is each function call part of this nest level limitation? 

    Each invocation you make of a new module or batch of dynamic SQL increases @@nestlevel with one. sp_executesql increases @@nestlevel with 2 since you first invoke sp_executesql and the then batch of dynamic SQL. When you exit a scope, @@nestlevel is decreased.

    While you can write recursive functions and procedures in T-SQL, it is not designed with the intention that recursion should be a commonly used feature. I don't know exactly what you are up to, since you did not post all the code, and you did not really describe the underlying problem. But you may be stretching the limits of what T-SQL is intended for, whence my suggestion to use C#.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    • Marked as answer by Ai-hua Qiu Wednesday, February 02, 2011 7:05 AM
    Wednesday, January 26, 2011 9:02 AM