Stored Procedure Nesting Level

 Hi All,

           Can anyone explain how to increase the nesting level of a stored procedure in SQL Server 2005 ???

 when  executing the stored procedure i'm getting  the following error ..............

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

Regards

Razik

Not sure if it is possible. In my knowledge it is not possible to increase the limit and you need to change your logic.

MSDN says:

Nested Triggers

 Nesting limit is 32 levels, SQL Server has no limit on the number of stored procedures that can be invoked from a given stored procedure, provided that the subordinate stored procedures do not invoke other subordinate stored procedures and the maximum nesting level is never exceeded.

You can view the nesting count using

select

@@NESTLEVEL

Refer this also : http://www.daniweb.com/forums/thread114466.html

Regards

REnju

http://blog.renjucool.com

Hi All,

         I got an alternative for recursive stored procedures, I used CTE (common table expressions) for recursive calling.

        I want to know is there any limitations for this also ?? How far it is effective in comparison with the other methods ???

Regards

Razik

I have not used it yet but heard it is very reliable for complex tasks. Go ahead.

http://msdn.microsoft.com/en-us/library/ms186243.aspx

Yes CTE is good approach Praveen.

Regards

Renju.R

SQL Server Performance Engineer

Thanks for the clarification Renju