Guru's Guide to SQL Server™ Stored Procedures, XML, and HTML, The

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 ...