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
system configuration function. To
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
EXECut_Factorial @WorkValIn, @WorkValOut OUTPUT SET @ValOut = @WorkValOut * @ValIn END ELSE SET @ValOut = 1 END
As the query optimizer
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
, as it
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)
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
Recursion of stored procedures should be avoided wherever possible, as it can cause more problems than it
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
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.
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.
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.
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
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.
Ignoring the fact that stored procedures will return data from a
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,
Working with errors
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
will always be 4
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.