Recursion

for RuBoard

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:

Listing 1-31 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.

for RuBoard