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.

Returning Values

In most of our stored procedures, we will either modify or return a set of data. You may also wish to return some value that signifies a successful or failure. In the remaining stored procedures, we will perform some business functionality, where we may return values calculated during that time.

There are different ways to return this sort of information and it depends on the number of values we wish to return and also how we wish to handle these within either other stored procedures or calling programs. Let's look at each method now.

RETURN

This is either implicit or explicit within a stored procedure, and will execute as the last statement of a stored procedure. This statement terminates the stored procedure immediately, with no further statements executing after this statement.

If RETURN is specified, then a value of 0 is returned, however, we can also return our own integer value. The normal practice is that a negative number returned indicates that an error has occurred. A positive number indicates some other reason, but it is often used to return the number of rows that have been returned or modified within a statement.

To demonstrate this, let's create a procedure that runs against the Categories table in northwind and returns the rows from the table and the number of rows found.

CREATE PROCEDURE sel_Categories AS BEGIN DECLARE @Rc INT SELECT CategoryName, Description FROM Categories SET @Rc = @@ROWCOUNT RETURN @Rc -- Any statement here will not execute END GO

If you are wish to execute this statement in the Query Analyzer, then you need to define it as:

DECLARE @RcRet INT

EXEC

@RcRet = sel_Categories SELECT @RcRet "No. Rows"

It is necessary to have a local variable defined for the return value to be placed at the end of the calling procedure. The final line simply prints out the return value.

Note

If you return the value from a local variable, the setting of that local variable cannot be NULL , and has to be a valid integer number.

The next section examines returning more than one value or non-integer values.

OUTPUT

Even if a RETURN statement is defined, we can supplement or replace this by output parameters. There is a limit of 2,100 to the number of parameters we can define. When we come close to this number, we should review what we are doing.

Defining an output parameter is similar to an input parameter – define the name of the variable prefixed with an @ sign, the data type, and the precision, where necessary, and then finally, the keyword OUTPUT .

After taking the stored procedure above and altering it slightly, we can see:

CREATE PROCEDURE sel_CategoriesWithOutput @Rcnt INT OUTPUT AS BEGIN SELECT CategoryName, Description FROM Categories SET @Rcnt = @@ROWCOUNT RETURN 1 -- Any statement here will not execute END GO

As we can see, we are still returning a value as well as the number of rows found in a parameter. To execute this stored procedure, we define the return code variable, as we did in the previous example, but also define the output parameter with the OUTPUT keyword:

DECLARE @OutParm INT, @RetVal INT EXEC @RetVal = sel_CategoriesWithOutput @OutParm OUTPUT SELECT @OutParm "Output Parm", @RetVal "Return Value"

Now we will look at the last method of retrieving information from a stored procedure.

Single Row of Data

Ignoring the fact that stored procedures will return data from a SELECT statement listing information that meets a specified set of criteria, it is also possible to return either a second set of records or a single row rowset that is the only information returned, neither of which can be attributed directly to a table. This is an unusual scenario and will only come about if you felt that the number of output parameters is excessive or if you are returning a set of information that will be simpler to use in the calling object as a tabular set of information rather than a set of values. A good example of this would be if the stored procedure was called as part of an Excel spreadsheet.

When an Error Occurs

Working with errors brings up several other considerations besides simply returning a value, which we will examine in detail in the next chapter.

Other Issues with Returning Values

It is possible to return a table as a data type from a stored procedure. A table data type is a special data type, and is simply a method to hold a temporary table in a variable rather than tempdb or your own database.

This data type should not be passed as the output parameter of a stored procedure, since the calling routine may not be able to handle that data type. If you want to return a rowset, it will be better to simply do that using the SELECT statement.

Ensure that the data types you are retrieving from a data source such as a table match the data types of the input and output parameters – there will definitely be problems when the data types don't match. For instance, a data type definition of CHAR(4) will always be 4 characters long. So if you placed ‘ TX ’ into a variable it will be ‘ TX ‘ (two spaces appended). However, it will always be stored as ‘ TX ’ with no spaces in a VARCHAR . Here you will only be sending two extra characters with CHAR , but if you are doing this many thousands of times, it becomes evident that VARCHAR be a better option.

Use VARCHAR wherever you can, and INT instead of BIGINT , to reduce the overhead of passing unnecessary data. Also send at least the minimum number of characters so that potential errors with a data type that is too small are not created.

Buy on amazon.com >>

Bain T., Davidson L., Dewson R.

<< Previous page

Table of contents

Next page >>