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

SQL Server 2000 Forums

Transact-SQL (2000)

Recursivity

Author

Topic

cidmi.dovic
Yak Posting Veteran

53 Posts

Posted - 2007-09-14 : 05:22:55

Hi,
I was trying to call inside a procedure to the same procedure and use recursivity to obtain the relationship between some records. Inside the sp i use cursors so I have the problem that SQL returns the cursor cur_XX already exists and is opened.

Is this posible to simulate recursivity on SQL server 2000?

Thanks in advance.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-14 : 05:26:23

The first question is "can this be done without the use of cursors"?

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-14 : 05:29:43

Are you trying to get Tree structures?

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-14 : 05:30:48

If you are doing what Madhi is asking, a simple WHILE loop will suffice.

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

cidmi.dovic
Yak Posting Veteran

53 Posts

Posted - 2007-09-14 : 05:35:24

Yes I'm trying to manage tree structures.

Andraax
Aged Yak Warrior

790 Posts

Posted - 2007-09-14 : 05:39:25

Recursivity is possible in SQL 2000, though I have only done it with UDF's, like the one under my post.

How did you declare the cursor in the procedure? Post some code, will make it easier for us to help.

/andraax

CREATE function fakultet (@i decimal(38,0))

returns decimal(38,0)

AS
begin
declare @res decimal(38,0)

select @res = case
when @i=1 then 1
else @i*dbo.fakultet(@i-1)
end

return @res
end

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-14 : 05:39:33

quote:
Originally posted by cidmi.dovic

Yes I'm trying to manage tree structures.

Refer
http://www.sqlteam.com/article/more-trees-hierarchies-in-sql

Madhivanan

Failing to plan is Planning to fail

cidmi.dovic
Yak Posting Veteran

53 Posts

Posted - 2007-09-14 : 05:40:40

Thanks a lot!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-14 : 07:29:00

quote:
Originally posted by Andraax

CREATE function fakultet (@i decimal(38,0))

returns decimal(38,0)

AS
begin
declare @res decimal(38,0)

select @res = case
when @i=1 then 1
else @i*dbo.fakultet(@i-1)
end

return @res
end

There are many things to improve

What if you call the function with a negative number?
It runs until out of memory.

alter function dbo.fakultet
(
	@i tinyint
)
returns decimal(38,0)
AS
begin
	declare @res decimal(38,0)

	if @i > 33
		return null
	else
		set @res = 1

	while @i > 1
		select	@res = @res * @i,
			@i = @i - 1

	return @res
end

My point is that if you can solve a problem without recursive method, chose that instead.

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-09-14 : 12:52:07

Recursive Cursors? yipes! :)

cidmi.dovic
Yak Posting Veteran

53 Posts

Posted - 2007-09-15 : 06:49:38

It's possible to use recursive cursors.

The only thing you have to do is define the cursors as local:

ALTER DATABASE DBName SET CURSOR_DEFAULT LOCAL

This way you can call your SP inside the SP and the cursor mantains his own information, not sharing the cursor between all executions...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-15 : 12:12:54

We understand that, but the issue is "why do you think you need cursor at all?".

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-17 : 01:35:28