Couple of days before I got a question on one of the forums asking for user defined function to calculate factorial of a number. I came up with the below function for calculating factorial and thought I would share it in my blog to benefit the wider audience.
Factorial as most of you know is calculated as follows
Factorial as most of you know is calculated as follows
n! = n*(n-1) *.(n-2) * …. * 1
Closely analyzing the formula above reveals the fact that it requires a recursive logic. This can be generated in T-SQL using tally or number table approach.
My preferred way of generating number table is by using CTE for recursion
The function is scalar valued and returns factorial result as an integer and takes an integer value as input
CREATE FUNCTION Factorial
(
@Number bigint
)
RETURNS float
AS
BEGIN
DECLARE @Result float
;With Product(N) AS
(
SELECT @Number
UNION ALL
SELECT N-1
FROM Product
WHERE N-1>0
)
SELECT @Result=COALESCE(@Result,1) * N
FROM Product
RETURN @Result
END
This function can be invoked as
SELECT dbo.factorial(12) AS Factorial
The result would be
Factorial
——————-
479001600
Hope this would come handy to anyone who want to use a logic for calculating factorial in t-sql. Any questions feel free to revert.
Hope this would come handy to anyone who want to use a logic for calculating factorial in t-sql. Any questions feel free to revert.
Could you also do something like this? It may be faster.
DECLARE @TABLE TABLE (num int)
INSERT INTO @TABLE
VALUES
(1),(2),(3),(4),(5)
SELECT EXP(SUM(log(num)))
from @table