Nesting in Stored Procedures

Recursion is a scenario where a procedure calls itself. You will rarely come across these, and try to avoid them wherever possible as they can create problems. At times, a simple error in the code, such as an unexpected input data parameter, can cause your server to hang due to tempdb filling up. You may come across this when working with interest calculations in bank transactions.

SQL Server puts a limit of 32 on the number of times that a stored procedure can be called by itself. However, things can go wrong even in that time frame, so it's always advisable to have a good ‘drop out’ scenario. One method is to check the recursion level that we are at by using the @@NESTLEVEL system configuration function. To demonstrate nesting levels through recursion, check the following system function that we have to add to SQL Server. This example calculates the factorial of a number:

     CREATE PROCEDURE ut_Factorial @ValIn bigint, @ValOut bigint output     AS     BEGIN       IF @ValIn > 20       BEGIN         PRINT 'Invalid starting point. Has to be <= 20'         RETURN -99       END       DECLARE @WorkValIn bigint, @WorkValOut bigint       IF @ValIn != 1         BEGIN           SET @WorkValIn = @ValIn - 1           PRINT @@NESTLEVEL           EXEC ut_Factorial @WorkValIn, @WorkValOut OUTPUT           SET @ValOut = @WorkValOut * @ValIn         END       ELSE         SET @ValOut = 1     END 

As the query optimizer tries to resolve the execution of ut_Factorial, which of course, doesn't exist until the stored procedure is placed into SQL Server, you will get the following error when you execute the above code.

Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object ‘ut_Factorial’. The stored procedure will still be created.

We know that the maximum nesting level will be the first value passed through @ValIn, as it reduces by one on each call to the procedure. We also have to check that we do not have more than a 20 level recursion invoked by the test at the top of the procedure.

We can see this in action with the following T-SQL code. The first digits listed will be the PRINT statement on the @@NESTLEVEL, which will show our current nesting level. We have to drop out here, if we have nested too many times:

     DECLARE @FactIn int, @FactOut int     SET @FactIn = 8     EXEC Factorial @FactIn, @FactOut OUTPUT     PRINT 'Factorial of ' + CONVERT(varchar(3),@FactIn) + ' is ' +     CONVERT(varchar(20),@FactOut) 

Note

If you execute a stored procedure that calls another stored procedure, the called stored procedure can access all objects created by the first stored procedure, including temporary tables, but cannot access values in the variables defined in the first procedure. Variable scope is local to that ‘call’.

Recursion of stored procedures should be avoided wherever possible, as it can cause more problems than it solves. This is true especially when we are not certain that the data will never cause a problem. Also, try to avoid creating temporary tables because if you attempt to recreate the table on the second or subsequent calls, it will already exist and the procedure will fail. In this scenario, we would create the temporary table if the @@NESTLEVEL is 1, and ignore that statement at any other call level.