# Factorial

Discussion in 'Contribute Your SQL Server Scripts' started by Madhivanan, Jan 19, 2005.

Hi,

This SP is used to find Factorial of a number

CREATE Procedure Factorial
(@Number numeric)
as
Declare @No numeric
Declare @i numeric
Declare @Product float

set @No=@Number
set @i=1
set @Product=1
C:
set @Product=@Product * @i
set @i=@i+1
if @i<=@No goto C
print @Product

Factorial 5 will give the result 120

2. ### FrankKalisModerator

Here's the recursive UDF approach

CREATE FUNCTION dbo.fakultÃ¤t(@n DECIMAL(38,0))
RETURNS DECIMAL(38,0)
AS
BEGIN
DECLARE @tmp DECIMAL(38,0)
IF (@n <= 1)
SELECT @tmp = 1
ELSE
SELECT @tmp = @n * dbo.fakultÃ¤t(@n - 1)
RETURN @tmp
END
GO
SELECT dbo.fakultÃ¤t(5)
DROP FUNCTION dbo.fakultÃ¤t
GO

--
Frank
http://www.insidesql.de
3. ### Luis MartinModerator

Just a suggestion: check for negative numbers.

Luis Martin
Moderator
SQL-Server-Performance.com

One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
Bertrand Russell

All postings are provided â€œAS ISâ€ with no warranties for accuracy.

For negative numbers the Factorial is always 1

5. ### FrankKalisModerator

No, that is not correct. Factorial is only defined for positive Integers, with the exception of 0, which is 1 by definition. I return 1 in my UDF, since this doesn't hurt in any multiplication or division, but that is just my convention.

--
Frank
http://www.insidesql.de
6. ### Luis MartinModerator

Yeap, if negative then error.

Luis Martin
Moderator
SQL-Server-Performance.com

One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
Bertrand Russell

All postings are provided â€œAS ISâ€ with no warranties for accuracy.

7. ### FrankKalisModerator

Normally I would agree here, but this is just for convenience (or call it laziness [<img src='/community/emoticons/emotion-5.gif' alt='' />] )<br /><br />--<br />Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />
8. ### Luis MartinModerator

Some people call me: The crazy tester[<img src='/community/emoticons/emotion-5.gif' alt='' />].<br /><br />Luis Martin<br />Moderator<br />SQL-Server-Performance.com<br /><br /><font size="1">One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important<br />Bertrand Russell<br /></font id="size1"><br /><br /><font size="1">All postings are provided â€œAS ISâ€ with no warranties for accuracy.</font id="size1"><br /><br /><br /><br />
9. ### tomandlisNew Member

This won't work for numbers greater than 32. you will generate the error below

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Where did you use that code?
Sounds different problem to me

Failing to plan is Planning to fail
11. ### dineshasankaModerator

I have suggestion. is it better to have int var for the input. Otherwise users will send decimal numbers as well.

In Addition, negative numbers need to validate

Well. Both the code will skip decimal part
Negative numbers will result in 1

Failing to plan is Planning to fail
13. ### FrankKalisModerator

quote:Originally posted by tomandlis

This won't work for numbers greater than 32. you will generate the error below

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
You're right, for any number > 32 it will fail. But in my environment that's no problem. Anyway, you can easily calculate this with a number helper table like master..spt_values

DECLARE @a FLOAT
SET @a = 1
SELECT @a = @a * Number
FROM master..spt_values
WHERE Number BETWEEN 1 AND 33
AND Type='P'
SELECT @a

-----------------------------------------------------
8.6833176188118859E+36

(1 row(s) affected)

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

>>But in my environment that's no problem.

What would be other environment?

Failing to plan is Planning to fail
15. ### FrankKalisModerator

Dunno, I guess any environment where you have the need to calcalate factorials &gt; 32. [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />

But I am able to find Factorial upto 170

Factorial 170

returns

7.257415615307994E+306

Failing to plan is Planning to fail
17. ### FrankKalisModerator

Hm, do you also need this for your job? That's what I meant. [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />

Not really now [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
19. ### Jeff ModenNew Member

I realize that I'm responding to a post more than 6 years old. ;-)

The largest factorial you can calculate using native datatypes in SQL Server is 170 for a total of 171 rows (if you include 0) which could be easily stored in a lookup table very much like a Tally Table. If you do find it inconvenient to do such lookups, we can take multiplication and recursion both out of the picture altogether. This one returns "1" for "0".

Code:
```WITH
cteTally AS
(
SELECT TOP 170
N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns
)
SELECT Factorial = ISNULL(POWER(CAST(10 AS FLOAT),SUM(LOG10(N))),1)
FROM cteTally```
As a bit of a side bar for those using rCTEs (recursive CTEs) to count, you might want to take a look at the following article for why using rCTEs to count even small numbers is a very bad idea.

http://www.sqlservercentral.com/articles/T-SQL/74118/
20. ### FrankKalisModerator

Wow Jeff,
this is sooo defensively written and way too much keystrokes...
Since I guess, it's pretty fair to assume that sys.all_columns returns at least 1 row, why not shorten it to:
Code:
```WITH cteTally AS
(SELECT TOP 170
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N
FROM
sys.all_columns
)

SELECT
POWER(10e0, SUM(LOG10(N))) Factorial
FROM
cteTally;
```