Friday, January 4, 2013

Calculating factorial in T-SQL

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

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.

1 comment:

  1. 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

    ReplyDelete