Nesting Stored Procedures
Stored procedures are nested when one stored procedure calls another. You can nest stored procedures up to 32 levels. The nesting level increases by one when the called stored procedure begins execution and decreases by one when the called stored procedure completes execution. Attempting to exceed the maximum of 32 levels of nesting causes the whole calling stored procedure chain to fail. The current nesting level for the stored procedures in execution is stored in the @@NESTLEVEL function.
Although the nesting limit is 32 levels, Microsoft® SQL Server™ 2000 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.
An error in a nested stored procedure is not necessarily fatal to the calling stored procedure. When invoking stored procedures within stored procedures, use the Transact-SQL RETURN statement to return a return code and check the return code from the calling stored procedure. In this way, you can specify the behavior of your stored procedures when errors occur. For more information about using return codes, see Returning Data Using a Return Code.
Stored procedures can even do a nested call to themselves, a technique known as recursion.