[SQL Server] SQL 2000 function Recursion Algorithm

-- SQL 2000 function RecursionAlgorithm
If object_id ('f _ test') is not null drop function f_test
Go
Create Function f_test (@ dt int)
Returns int
As
Begin
Declare @ I int
Select @ I = @ DT + isnull (DBO. f_test (number), 0) from Master .. spt_values where type = 'p' and number = @ dt-1
Return @ I
End
Go
Select DBO. f_test (10)

/*

-----------
55

(One row is affected)

*/

 

-- SQL 2000 function recursive algorithm 2
If object_id ('f _ test') is not null drop function f_test
Go
Create Function f_test (@ dt int)
Returns numeric (10, 4)
As
Begin
Declare @ I numeric (10, 4)
Select @ I = Number + isnull (DBO. f_test (number), 0) * 0.7 from Master .. spt_values where type = 'p' and number = @ dt-1
Return @ I
End
Go
Select number + DBO. f_test (number) * 0.7 from Master .. spt_values where type = 'p' and number = 3

--- Select (2 + 0.7) * 0.7 + 3
/*
---------------------------------------
4.89000

(One row is affected)

*/