This blog is part of the series The TSQL Classes: Stored Procedure.

In this class I’ll talk about SQL Server TSQL nested procedures, recursion and plan caching. Let’s begin with creating a simple procedure to add two numbers.

Create procedure to add two numbers

The sum can be returned in 2 ways.

  1. Using OUTPUT parameter.
  2. Using SELECT statement.

The above code is self explanaotry due to the inline comments. One thing to add is the use of return statement. The calling application/procedure can use @ReturnStatus variable to detect successful/unsuccessful execution of the stored procedure.

Nested Procedures

As the heading goes, nested means procedure with in a procedure. The nesting limit in SQL server is up to 32 procedures; however there is no limit on number of procedure that can be called from a single procedure. The nesting level of the current procedure can be returned using @@Nestlevel function.

Let’s look at a simple nesting example.


In above example, when executed directly the nesting level of usp_IamInnerProc is 1 where as when executed from usp_IamOuterProc the nesting level of usp_IamInnerProc is 2. This is how SQL server keeps track of nesting levels and will terminates the procedures once nesting level crosses the limit of 32.


Recursion is when a procedure calls itself until a stop condition is reached. However, due to limit on nesting levels, recursion is limited to 32 calls. The classic example of recursion is calculating a factorial of a number.

The above code when executed returns 24 which is the factorial of 4.

Plan Caching

A Stored procedure when first executed is compiled and the execution plan is saved for future use. During next execution of stored procedure, SQL server optimizer picks the saved execution plan unless until the procedure is not recompiled. We’ll look into procedure recompile as the class progresses.

The cached plan details are stored in sys.dm_exec_cached_plans DMV.  The below query returns all cached procedure plans.

Let’s look at the cached plan of procedure  usp_add_output_parameter  created earlier to add two numbers.



The SQL Server optimizer compiles and saves the execution plan for the procedure usp_add_output_parameter as shown in above snapshot. The DMV dm_exec_cached_plans returns lot of details about cached plans, the description of which can be found here  The plan_handle is used to get the xml plan and query text of the cached object. The usecounts tells the number of times a plan is used for procedure execution.  A “usecounts” of 100 means a plan has been used 100 times for a compiled query or a procedure. A benefit of plan caching is that stored procedures need not to be compiled every time it is executed. SQL Server Optimizer uses the cached plan for stored procedure execution.


In this class I created a simple stored procedure to add two numbers and talked about Nested procedures, recursion and plan caching.



Ahmad Osama

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook