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 1-31 presents an example that features a stored procedure that calculates the factorial of a number:
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.