Professional SQL Server™ 2005 Programming

11.9. A Brief Look at Recursion

Recursion is one of those things that aren't used very often in programming. Still when you need it, there never seems to be anything else that will quite do the trick. As a just-in-case, a brief review of what recursion is seems in order.

The brief version is that recursion is the situation where a piece of code calls itself. The dangers here should be fairly self-evident. If it calls itself once, then what's to keep it from calling itself over and over again? The answer to that is you. That is, you need to make sure that if your code is going to be called recursively, you provide a recursion check to make sure you bail out when it's appropriate.

I'd love to say that the example I'm going to use is neat and original, but it isn't. Indeed, for an example, I'm going to use the classic recursion example that's used with about every textbook recursion discussion I've ever seen. Please accept my apologies; it's just that it's an example that just about anyone can understand, so here we go.

The classic example uses factorials. A factorial is the value you get when you take a number and multiply it successively by that number less one, then the next value less one, and so on until you get to 1. For example, the factorial of 5 is 120 — that's 5*4*3*2*1.

Look at an implementation of such a recursive sproc:

CREATE PROC spFactorial @ValueIn int, @ValueOut int OUTPUT AS DECLARE @InWorking int DECLARE @OutWorking int IF @ValueIn != 1 BEGIN SELECT @InWorking ...