iterative solution does not have the restriction 32 nesting levels : Recursive function « Procedure Function « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. Procedure Function
3. Recursive function

6> CREATE PROC factorial2 @param1 int, @answer NUMERIC(38,0) OUTPUT
7> AS
8> DECLARE @counter int
9> IF (@param1 < 0 OR @param1 > 33)
10>     BEGIN
11>         RAISERROR ('Illegal Parameter Value. Must be between 0 and 33',
12>             16, -1)
13>         RETURN -1
14>     END
15>
16> SET @counter=1 SET @answer=1
17>
18> WHILE (@counter < @param1 AND @param1 <> 0 )
19>     BEGIN
20>         SET @answer=@answer * (@counter + 1)
21>         SET @counter=@counter + 1
22>     END
23>
24> RETURN
25> GO
1>
2> DECLARE @answer numeric(38, 0), @param int
3> SET @param=0
4> WHILE (@param <= 32)
5>     BEGIN
6>         EXEC factorial2 @param, @answer OUTPUT
7>         PRINT CONVERT(varchar(50), @param) + '! = '
8>             + CONVERT(varchar(50), @answer)
9>         SET @param=@param + 1
10>     END
11> GO
0! = 1
1! = 1
2! = 2
3! = 6
4! = 24
5! = 120
6! = 720
7! = 5040
8! = 40320
9! = 362880
10! = 3628800
11! = 39916800
12! = 479001600
13! = 6227020800
14! = 87178291200
15! = 1307674368000
16! = 20922789888000
17! = 355687428096000
18! = 6402373705728000
19! = 121645100408832000
20! = 2432902008176640000
21! = 51090942171709440000
22! = 1124000727777607680000
23! = 25852016738884976640000
24! = 620448401733239439360000
25! = 15511210043330985984000000
26! = 403291461126605635584000000
27! = 10888869450418352160768000000
28! = 304888344611713860501504000000
29! = 8841761993739701954543616000000
30! = 265252859812191058636308480000000
31! = 8222838654177922817725562880000000
32! = 263130836933693530167218012160000000
1>
2> drop PROC factorial2;
3> GO