SQL Server TSQL – The TSQL Classes Stored Procedures: Nested proc, Recursion And Plan Caching

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.

-- using output parameter to return sum
-- of two numbers
CREATE PROCEDURE dbo.usp_add_output_parameter
    @p1 int= 10,
    @p2 int= 12,
    @p3 intOUTPUT
AS
BEGIN
    -- Add two numbers
    SELECT @p3 = @p1 + @p2
END
GO
-- Execute procedure 
DECLARE @ReturnStatus int,@p3_Output int
Execute @ReturnStatus= usp_add_output_parameter 100,200,@p3_Output OUTPUT
SELECT [ReturnStatus]=@ReturnStatus, [Sum]=@p3_Output
 
-- using select statement to output sum
-- of two numbers
CREATE PROCEDURE dbo.usp_add_select
    @p1 int= 10,
    @p2 int= 12
AS
BEGIN
    -- Add two numbers
    SELECT  [Sum]=@p1 + @p2
END
GO
-- Execute procedure
DECLARE @ReturnStatus int
Execute @ReturnStatus= usp_add_select 100,200
SELECT @ReturnStatus

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.

CREATE PROC usp_IamInnerProc
AS
BEGIN
    SELECT [Message]='I am inner procedure.Called from usp_IamOuterProc!!!',
           [InnerProcNestingLevel]='My Nesting Level is '+Ltrim(str(@@NestLevel))
END
GO
CREATE PROC usp_IamOuterProc
AS
BEGIN  
    -- call inner proc
    Execute usp_IamInnerProc
    SELECT [Message]='I am Outer Procedure!!!',
           [InnerProcNestingLevel]='My Nesting Level is '+Ltrim(str(@@NestLevel))
END
 
-- Execute Inner Proc
Execute usp_IamInnerProc
-- Execute Outer Proc
Execute usp_IamOuterProc

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

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.

CREATE PROCEDURE [dbo].[GetFactorial]
 (
    @Num int,
    @factorial int OUTPUT
 )
 AS
 BEGIN
    DECLARE @n_1 int
    DECLARE @factemp int
    IF @Num != 1
        BEGIN
        SELECT @n_1 = @Num - 1
        -- recursive call to GetFactorial with value n - 1 
        -- untill @num > 1
        EXEC GetFactorial @n_1, @factemp OUTPUT
       -- Multiply n * n-1 
        SELECT @factorial = @Num * @factemp
    END
        ELSE
            BEGIN
                SELECT @factorial = 1
            END
 END
  
 Declare @factorial int
 Exec GetFactorial 4,@factorial OUTPUT
 select @factorial

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.

SELECT
    DatabaseName=db_name(qrytxt.dbid), 
    ObjectName=Object_Name(qrytxt.objectid),
    qrytxt.text, 
    qryplan.query_plan,
    cacheobjtype, 
    usecounts,
    objtype
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) qrytxt
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qryplan
WHERE objtype='proc'

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

SELECT
    DatabaseName=db_name(qrytxt.dbid), 
    ObjectName=Object_Name(qrytxt.objectid),
    qrytxt.text, 
    qryplan.query_plan,
    cacheobjtype, 
    objtype
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) qrytxt
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qryplan
WHERE qrytxt.objectid=object_id('usp_add_output_parameter');

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 http://msdn.microsoft.com/en-us/library/ms187404.aspx.  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.

Summary

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

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