Introduction

When one stored procedure calls another stored procedure (SP) you have what is called stored procedure nesting. I often call such stored procedures a switching yard because we often create one stored procedure that executes 1 of many SP’s based on what parameters are sent in. The nest level increases each time a stored procedure calls another SP. When the nest level exceeds 32 the transaction will stop with an error.

Display Nest Level

You can view the nest level in a stored procedure by using the following command:

SELECT @@NESTLEVEL

If not executed within a stored procedure it will show 0. @@NESTLEVEL is one of the many objects once called a global variable. BOL now calls them functions.

Exceeding The Max Nest Level

In this example you will see a simple way to exceed the max nest level. I developed a stored procedure that will create 31 stored procedures. The first SP will call the second SP which will call the third SP and so on. Here is the stored procedure:

CREATE PROCEDURE spNesting1 AS
DECLARE @Query varchar(1000), @Count varchar(2)
SET @Count = '2'
CREATE TABLE TestNestLevel (NestLevel int)
WHILE @Count <= 32
BEGIN
SET @Query = 'CREATE PROCEDURE spNesting' + @Count + ' AS'
+ CHAR(10) + CHAR(10) + 'SELECT @@NESTLEVEL AS [Nest Level For spNesting' + @Count + ']'
+ CHAR(10) + CHAR(10) + 'INSERT INTO TestNestLevel (NestLevel) SELECT @@NESTLEVEL'
+ CHAR(10) + CHAR(10) + 'EXEC spNesting' + LTRIM(STR(@Count + 1))
EXEC (@Query)
SET @Count = @Count + 1
END
SELECT @@NESTLEVEL AS [Nest Level For spNesting1]
INSERT INTO TestNestLevel (NestLevel) SELECT @@NESTLEVEL
EXEC spNesting2

Now execute the stored procedure.

After completion you will find the following error:

Server: Msg 217, Level 16, State 1, Procedure spNesting31, Line 7

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

It is interesting to note that the stored procedure that would have caused the max nesting level to be exceeded does not exist. SQL Server didn’t bother to check if it existed or not since executing it would have exceeded the max nesting level. Now look in the table called TestNestLevel. You should see the numbers 1 thru 32.

To better see that all execution halts as soon as you exceed the max nesting level you need to move the lines that execute the next stored procedure so that it comes before the lines that have the INSERT statements. Now drop the SP’s numbered 2 thru 32 and drop the table called TestNestLevel. Rerun spNesting1. Now select all the rows from TestNestLevel. There should be none. This is because all 32 stored procedures stopped executing before getting to their INSERT statements.

You can also have a stored procedure call itself. This type of nesting is called recursion and you will get similar results as those shown above if you exceed the max nesting level.

A Simple Nested Stored Procedure

Often I create a stored procedure I refer to as a switching yard. Its only purpose is to give an application one Sp to execute. It uses the parameters sent into it to determine which one of many similar SP’s to execute. Here is a simple example:

CREATE PROCEDURE spAuthors (@Last varchar(20) = '', @First varchar(20) = '') AS
IF @Last <> '' AND @First = ''
BEGIN
EXEC spAuthorsLast @Last = @Last
RETURN
END
IF @First <> '' AND @Last = ''
BEGIN
EXEC spAuthorsFirst @First = @First
RETURN
END
EXEC spAuthorsVarious @Last = @Last, @First = @First
CREATE PROCEDURE spAuthorsLast (@Last varchar(20)) AS
SELECT * FROM authors WHERE au_lname LIKE @Last + '%'
CREATE PROCEDURE spAuthorsFirst (@First varchar(20)) AS
SELECT * FROM authors WHERE au_fname LIKE @First + '%'
CREATE PROCEDURE spAuthorsVarious (@Last varchar(20) = '', @First varchar(20) = '') AS
SELECT * FROM authors WHERE au_lname LIKE @Last + '%' AND au_fname LIKE @First + '%'

Only one of the three SP’s will get executed upon executing spAuthors. I usually have one SP at the end of my switching yard that is not protected by any IF statements. That SP can handle all possible combinations. Because it must handle all situations it is often dynamic and not as optimized as the other SP’s. I leave it without an IF so that something will execute in case I fail to cover all possibilities (usually many more than the 3 shown in spAuthors). The RETURN keyword after each SP in an IF statement simply causes spAuthors to stop executing and so ensures that only one of the three SP’s will be executed each time spAuthors is run.

Why Nest Stored Procedures

I believe there are many reasons to nest stored procedures. I will only discuss one here. Nesting stored procedures allows you to break up large amounts of SQL into smaller, more manageable pieces. By dividing one SP into various pieces as in the example above you may reduce the time it takes to find and modify code. Using the above example, lets say we wanted to change the like search for the first name. We only need to make changes to spAuthorsFirst and spAuthorsVarious. We wouldn’t need to look at the other two SP’s. The advantage becomes more visible when you have 1,000 or more lines of code per SP and you have 5 to 20 SP’s being called by one main SP.

Conclusions

In this article I described how you can nest stored procedures and demonstrated the effects of exceeding the max nesting level. I also showed how you can see at what nesting level each stored procedure is at. Nesting stored procedures helps you create modular code that is easy to maintain.