SP nesting in SQL

Question

Hi Friends How many level SP nesting possible in SQL ?

Santhosh Kumar Jayaraman · Answer

Stored procedures are nested when one stored procedure calls another or executes managed code by referencing a CLR routine type or aggregate. You can nest stored procedures and managed code references up to 32 levels. The nesting level increases by one when the called stored procedure or managed code reference begins execution and decreases by one when the called stored procedure or managed code reference completes execution. Attempting to exceed the maximum of 32 levels of nesting causes the whole calling chain to fail. The current nesting level for the stored procedures in execution is stored in the @@NESTLEVEL function. Source: http://msdn.microsoft.com/en-us/library/ms190607(v=sql.105).aspx