SQL Functions - factorial
Here is a recursive solution:
CREATE FUNCTION dbo.Factorial ( @iNumber int )
RETURNS INT
AS
BEGIN
DECLARE @i int
IF @iNumber <= 1
SET @i = 1
ELSE
SET @i = @iNumber * dbo.Factorial( @iNumber - 1 )
RETURN (@i)
END
A non recursive way
;With Nums As
(
select ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS RN
FROM sys.objects
)
SELECT POWER(10.0, SUM(LOG10(RN)))
FROM Nums
WHERE RN <= 10
And a recursive way
declare @target int
set @target=10;
WITH N AS
(SELECT 1 AS i,
1 AS f
UNION ALL
SELECT i+1,
f*(i+1)
FROM N
WHERE i < @target
)
SELECT f FROM N
WHERE i=@target
-- Iterative method. -- Why Iterative? It is simpler and faster. -- For @N from 0 to 20 this gives an exact result. -- 21 will give an overflow.
DECLARE @N Bigint = 20
DECLARE @F Bigint = 1
WHILE @N > 0 BEGIN
SET @F = @f*@n
SET @N = @N-1
END
SELECT @F AS FACTORIAL
-- Change the datatype to float and you can get the factorial up to 170. -- 171 will result in an overflow. -- Remark the result will only be accurate over a limited number of positions.
DECLARE @N FLOAT = 170
DECLARE @F FLOAT = 1
WHILE @N > 0 BEGIN
SET @F = @f*@n
SET @N = @N-1
END
SELECT @F AS FACTORIAL
-- Ben