Because Transact-SQL supports recursion, you can write stored procedures that call themselves. Recursion can be defined as a method of problem solving wherein the solution is arrived at by repetitively applying it to subsets of the problem. A common application of recursive logic is to perform numeric computations that lend themselves to repetitive evaluation by the same processing steps. Listing 131 presents an example that features a stored procedure that calculates the factorial of a number:
Listing 131 Stored procedures can call themselves recursively.
SET NOCOUNT ON USE master IF OBJECT_ID('dbo.sp_calcfactorial') IS NOT NULL DROP PROC dbo.sp_calcfactorial GO CREATE PROC dbo.sp_calcfactorial @base_number decimal(38,0), @factorial decimal(38,0) OUT AS SET NOCOUNT ON DECLARE @previous_number decimal(38,0) IF ((@base_number>26) and (@@MAX_PRECISION<38)) OR (@base_number>32) BEGIN RAISERROR('Computing this factorial would exceed the server''s max. numeric precision of %d or the max. procedure nesting level of 32',16,10,@@MAX_PRECISION) RETURN(-1) END IF (@base_number<0) BEGIN RAISERROR('Can''t calculate negative factorials',16,10) RETURN(-1) END IF (@base_number<2) SET @factorial=1 -- Factorial of 0 or 1=1 ELSE BEGIN SET @previous_number=@base_number-1 EXEC dbo.sp_calcfactorial @previous_number, @factorial OUT -- Recursive call IF (@factorial=-1) RETURN(-1) -- Got an error, return SET @factorial=@factorial*@base_number IF (@@ERROR<>0) RETURN(-1) -- Got an error, return END RETURN(0) GO DECLARE @factorial decimal(38,0) EXEC dbo.sp_calcfactorial 32, @factorial OUT SELECT @factorial
The procedure begins by checking to make sure it has been passed a valid number for which to compute a factorial. It then recursively calls itself to perform the computation. With the default maximum numeric precision of 38, SQL Server can handle numbers in excess of 263 decillion. (Decillion is the U.S. term for 1 followed by 33 zeros. In Great Britain, France, and Germany, 1 followed by 33 zeros is referred to as 1,000 quintillion.) As you'll see in Chapter 11, UDFs functions are ideal for computations like factorials.