SQL Challenge for Beginners - Find Factorial

The Challenge for Beginners

This challenge is not a real time problem directly, but it measures about logical thinking. The problem is all about finding the factorial of numbers. Though it is known to most of us what a factorial is, but to recall the concept here is an example:

Factorial of 3 is 1*2*3 = 6 i.e. the factorial of a non-negative integer n, denoted by n!, is the product of all positive integers less than or equal to n.

Sample Input Data

Nums
-----------
0 
1 
3 
5 
10

Expected Output

Nums        Factorial
----------- -----------
0                    1
1                    1
3                    6
5                  120
10             3628800

Script

Here is the script to generate the sample data

DECLARE @Fact TABLE(Nums INT)
INSERT INTO @Fact 
SELECT 0 UNION ALL  
SELECT 1 UNION ALL  
SELECT 3 UNION ALL  
SELECT 5 UNION ALL  
SELECT 10

SELECT * FROM @Fact

Solution :

DECLARE @Fact TABLE(Nums INT)
INSERT INTO @Fact 
SELECT 0 UNION ALL 
SELECT 1 UNION ALL 
SELECT 3 UNION ALL 
SELECT 5 UNION ALL 
SELECT 10

DECLARE @no INT,@f BIGINT,@nums INT

DECLARE C1 CURSOR FOR SELECT nums FROM @fact
OPEN C1
FETCH NEXT FROM C1 INTO @no
WHILE @@FETCH_STATUS=0
BEGIN
        SELECT @f=1,@nums=@no
        WHILE @no>0
        BEGIN
             SET @f=@f*@no
             SET @no=@no-1 
        END
        DECLARE @t TABLE(nums INT,Fact BIGINT)
        INSERT INTO @T SELECT @Nums,@F
        FETCH NEXT FROM C1 INTO @no
END
CLOSE C1
DEALLOCATE C1 

SELECT * FROM @T ORDER BY 1

Just Paste Above solution in your SQL Server You will get a

Output that really you need.