December 16, 2008 at 10:03 am
Hello,
I wrote a recursive stored procedure and I encountered a problem with the nesting level. The error message is
Msg 217, Level 16, State 1, Procedure sp_executesql, Line...
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
I read that this is a common bug until SQL2008 but I was pretty sure that I had not yet reached the level. So I checked the level every time the procedure called itself. The output showed that the level increased like 1-4-7-10-13-... I thought that the level should increase by 1 every time the procedure calls itself.
Do you have an idea why the level increases by 3?
The only thing that could have something to do with it is the fact, that no SQL code is executed "directly" in the procedure. The SQL string is built up and executed via sp_executesql. I had to do it like this because I need cursors in the procedure, got a problem with already existing cursors and had to build up cursor names dynamically.
Hope to hear from you!
Peter
December 16, 2008 at 10:23 am
OK, I think I know what causes the 3 step increase. The stored procedure structure is like that:
PROCEDURE
BEGIN
...DECLARE @sql NVARCHAR
...SET @sql = [...call PROCEDURE again...]
...EXEC SP_EXECUTESQL @sql
END
I guess the recursion causes the first increase, the SP_EXECUTESQL causes the next and finally EXEC causes the last increase. That makes 3. Could it be like that?
Is there another chance to execute an SQL string inside a stored procedure? I need to build the SQL string before executing it because I need dynamic cursors. Is there anything I could do except working with temporary tables?
Peter
December 16, 2008 at 11:12 am
So, let me make sure I understand. You've written a recursive, cursor driven dynamic sql query... on purpose?
December 16, 2008 at 11:23 am
Garadin (12/16/2008)
So, let me make sure I understand. You've written a recursive, cursor driven dynamic sql query... on purpose?
Recursive problems require recursive actions. So...
December 16, 2008 at 1:47 pm
PeterTL (12/16/2008)
Recursive problems require recursive actions. So...
If you don't know other way it does not mean there is no other way.
_____________
Code for TallyGenerator
December 17, 2008 at 1:11 am
Sergiy (12/16/2008)
PeterTL (12/16/2008)
Recursive problems require recursive actions. So...If you don't know other way it does not mean there is no other way.
Yes I guess so. You have an idea how to resolve hierarchical lists of materials? I could try to reduce the 3 step increase to 1 (replace cursors with temporary tables) but the hierarchical and therefore recursive problem stays the same. This would allow to process up to 32 steps compared to 32/3 steps but it will happen that 32 steps are not enough, too.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply