Home > Articles > Data > SQL Server

  • Print
  • + Share This
This chapter is from the book

Recursion

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.

  • + Share This
  • 🔖 Save To Your Account