Programming in MS SQL Server 2012

Nested Stored Procedure

Procedures are nested when one procedure call another or executes managed code by referencing a CLR routine, type, or aggregate. Procedures and managed code references can be nested up to 32 levels. The nesting level increases by one when the called procedure or managed code reference begins execution and decreases by one when the called procedure or managed code reference completes execution. Methods invoked from within the managed code do not count against the nesting level limit. However, when a CLR stored procedure performs data access operations through the SQL Server managed provider, an additional nesting level is added in the transition from managed code to SQL.
Attempting to exceed the maximum nesting level causes the entire calling chain to fail. You can use the @@NESTLEVEL function to return the nesting level of the current stored procedure execution.

-- Example 129 --

CREATE PROCEDURE dbo.usp_NestedProcedure

@Dept_Name varchar(50),

@Phone varchar(50)

BEGIN

EXEC EBusiness.dbo.usp_EmployeePhoneList @Phone

EXEC EBusiness.dbo.usp_DeptEmployeeList @Dept_Name

END

EXEC dbo.usp_NestedProcedure 'System','10%'

Query Output Screen

* * * * *

Your Comment To AUTHOR