Recursively call itself : Recursive function « Procedure Function « SQL Server / T-SQL Tutorial

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

2> CREATE PROC factorial @param1 decimal(38,0), @answer decimal(38,0) output
3> AS
4> DECLARE @one_less decimal(38,0), @status int
5>
6> IF (@param1 < 0 OR @param1 > 32)
7>     BEGIN
8>         -- Illegal parameter value. Must be between 0 and 32.
9>         RETURN -1
10>     END
11>
12> IF (@param1=0 or @param1=1)
13>     SET @answer=1
14> ELSE
15>     BEGIN
16>         SET @one_less=@param1 - 1
17>         EXEC @status=factorial @one_less, @answer output
18>         
19>         IF (@status= -1)
20>             BEGIN
21>                 RETURN -1
22>             END
23>
24>         SET @answer=@answer * @param1
25>
26>         IF (@@ERROR <> 0)
27>             RETURN -1
28>     END
29>
30> RETURN  0
31> GO
1>
2>
3> DECLARE @answer decimal(38,0), @param int
4> SET @param=0
5> WHILE (@param <= 32) BEGIN
6>     EXEC  factorial  @param, @answer output
7>     IF (@answer= -1)
8>         BEGIN
9>             RAISERROR('Error executing factorial procedure.', 16, -1)
10>             RETURN
11>         END
12>     PRINT CONVERT(varchar, @param) + '! = ' + CONVERT(varchar(50), @answer)
13>     SET @param=@param + 1
14> END
15> 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 factorial;
3> GO
1>