Recursion - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Recursion

Author

Topic

Bhanu_2007
Starting Member

10 Posts

Posted - 2007-11-12 : 04:53:45

How to write a recursive Procedure in SQL Server to find factorial of 50?

The recursive call is limited to 32..could any one help me out.

Thanks in Advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-12 : 05:05:02

Why do you ask this question again? Microsoft SQL Server does not support greater recursion level than 32.
We have told you numerous times! Both on this forum and other forums.

CREATE FUNCTION dbo.fnFactorial
(
	@n BIGINT
)
RETURNS BIGINT
AS

BEGIN
    DECLARE @i BIGINT

    IF @n >= 0 AND @n <= 20
        SELECT @i = 1

    WHILE @n > 1
        SELECT @i = @i * @n,
               @n = @n - 1

    RETURN @i
END

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-12 : 05:25:59

quote:
Originally posted by Bhanu_2007

How to write a recursive Procedure in SQL Server to find factorial of 50?

The recursive call is limited to 32..could any one help me out.

Thanks in Advance

Where would you need factorial of 50?

Use this

declare @a float
set @a = 1

select @a=@a*number from master..spt_values
where type='p' and number between 1 and 50

select @a

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-12 : 06:12:41

30414093201713378043612608166064768844377641568960512000000000000

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-12 : 06:23:55

quote:
Originally posted by Peso

30414093201713378043612608166064768844377641568960512000000000000

E 12°55'05.25"
N 56°04'39.16"

Yes if OP wants to handle that number

Madhivanan

Failing to plan is Planning to fail

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-11-12 : 06:26:59

dear Madhi,
what is spt_values in master database.....

Vinod
Even you learn 1%, Learn it with 100% confidence.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-12 : 06:34:44

It is a predefined numbers table that are very useful in various situations.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-12 : 06:35:38

Just do a

SELECT * FROM master..spt_values

so see what you get. Then write

SELECT * FROM master..spt_values WHERE Type = 'p'

and check the result again.

E 12°55'05.25"
N 56°04'39.16"

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-11-13 : 01:24:41

Great Teacher Mr.Peso

Vinod
Even you learn 1%, Learn it with 100% confidence.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-11-13 : 01:30:53

and how did you expand the value
3.0414093201713376E+64 ?

Vinod
Even you learn 1%, Learn it with 100% confidence.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-13 : 02:25:06

quote:
Originally posted by sunsanvin

and how did you expand the value
3.0414093201713376E+64 ?

Vinod
Even you learn 1%, Learn it with 100% confidence.

3.0414093201713376*10^64

Madhivanan

Failing to plan is Planning to fail

IceDread
Yak Posting Veteran

66 Posts

Posted - 2007-11-13 : 06:49:07

Recursion is bad, relly relly bad from my experience at least.

Check out the execution plan for your recursion and then compare that with the execution plan for non recursion and also notice the big difference in speed.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources