Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Nesting Stored Procedures

By Robert Marda, (first published: 2003/05/23)

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.
Total article views: 35046 | Views in the last 30 days: 23
 
Related Articles
FORUM

nesting stored procedures

executing two nesting levels of stored procedures

FORUM

Rights to execute stored procedure

Rights to execute stored procedure

FORUM

Main Store procedure to execute several store procedures or Trigger?

Main Store procedure to execute several store procedures or Trigger that executes a store procedure?...

FORUM

Execute SSIS package stored in Database - From Stored Procedure

Execute SSIS package stored in Database - From Stored Procedure

Tags
stored procedures    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones