Factorial

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

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

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

Madhivanan

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

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.

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 />

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 />

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


Madhivanan

Failing to plan is Planning to fail

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


Madhivanan

Failing to plan is Planning to fail

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?

Madhivanan

Failing to plan is Planning to fail

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


Madhivanan

Failing to plan is Planning to fail

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

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;