Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Recursive stored procedure - help needed!

4 posts in Windows NT Last posting was on 1997-12-31 15:11:15.0Z
Charles Butcher Posted on 1997-12-30 10:49:16.0Z
Message-ID: <34A8D1AC.AFF@ozemail.com.au>
Date: Tue, 30 Dec 1997 21:49:16 +1100
From: Charles Butcher <butchc@ozemail.com.au>
Reply-To: butchc@ozemail.com.au
X-Mailer: Mozilla 3.01Gold (Win95; I)
MIME-Version: 1.0
Subject: Recursive stored procedure - help needed!
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 60
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5329
Article PK: 1081407

I've been having some trouble getting a recursive stored procedure
to work properly with SQL Server. I wrote the procedure some time
ago for SQL Anywhere, but using the Transact-SQL "dialect" because
I knew I'd need to make it work with SQL Server down the track.

The procedure works on a table called "pr_property" which stores
a property hierarchy. Each property may belong to a "parent"
property. eg. there be rooms rolling up to floors rolling
up to buildings (or something similar). The stored procedure needs
to be able to calculate the floor area of any "property" in the
hierarchy be summing the floor area's from all the "leaf nodes"
of the tree (those that don't have any children). This seemed like
a naturally recursive problem, so I came up with the code below:

create procedure sp_pr_calc_floor_area(@pr_no integer,@area decimal(15)
output)
/*
This procedure calculates the floor area for a property by
recursively summing the floor area of all "leaf nodes" below
this property in the hierarchy.
*/
as
begin
declare @child integer
declare @child_area decimal(12)
select @area=0
declare cur_children dynamic scroll cursor for
select pr_no from pr_property where parent_pr_no=@pr_no
open cur_children
fetch next cur_children into @child
if @@sqlstatus=0
begin
while(@@sqlstatus=0)
begin
execute sp_pr_calc_floor_area @child,@child_area
select @area=@area+@child_area
fetch next cur_children into @child
end
close cur_children
deallocate cursor cur_children
end
else
/* We've reached a property with no children */
select @area=(select pr_floor_area from pr_property where
pr_no=@pr_no)
end

This code works very nicely with SQL Anywhere, but I'm having trouble
with the SQL Server version. I had to make a few small modifications
to the syntax - no problem there, but now when I execute the procedure,
the first recursive call generates an error complaining that the
cursor cur_children already exists.

Can someone explain the scoping of cursors within recursive procedures?
How do SQL Server and SQL Anywhere differ in this area?

Is there an easy way to get around my problem or do I need to try a
completely different approach?

Any help/suggestions would be greatly appreciated!

Thanks,
Charles Butcher


Kevin Campbell Posted on 1997-12-31 15:11:15.0Z
Message-ID: <34AA6093.7E36737F@gte.net>
Date: Wed, 31 Dec 1997 07:11:15 -0800
From: Kevin Campbell <KCampbel@gte.net>
Organization: Ad Rem, Inc.
X-Mailer: Mozilla 4.04 [en] (WinNT; I)
MIME-Version: 1.0
Subject: Re: Recursive stored procedure - help needed!
References: <34A8D1AC.AFF@ozemail.com.au>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 8
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5323
Article PK: 1081400

That's an interesting problem. Cursors have no restrictions to scope,
effectively they are globally scoped within your session.

I guess my choice might lean towards a #temp table. I'm not sure what
happens if a recursively called proc creates the same #temp table twice.

Kevin


Kent Friberg Posted on 1997-12-31 11:31:58.0Z
From: "Kent Friberg" <kefriber@NoSpam.online.no>
References: <34A8D1AC.AFF@ozemail.com.au>
Subject: Re: Recursive stored procedure - help needed!
Date: Wed, 31 Dec 1997 12:31:58 +0100
Lines: 18
X-Newsreader: Microsoft Outlook Express 4.71.1712.3
X-MimeOLE: Produced By Microsoft MimeOLE V4.71.1712.3
Message-ID: <ys05WcdF9GA.189@forums.powersoft.com>
Newsgroups: sybase.public.sqlserver.nt
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5325
Article PK: 1081402


Charles Butcher wrote in message <34A8D1AC.AFF@ozemail.com.au>...
>I've been having some trouble getting a recursive stored procedure
>to work properly with SQL Server. I wrote the procedure some time
>ago for SQL Anywhere, but using the Transact-SQL "dialect" because
>I knew I'd need to make it work with SQL Server down the track.

<...snipped sections....>

I do not have the Sybooks nor a server at home either, but I'm quite
positive that you can not open a cursor with one name twice, you'll have to
do a close in between. So my guess is that (if my assumtion is correct)
you'll have to rethink/rewrite your sp. <sigh> :-) I have not used
recursion since TurboPascal in HighScool (loooong ago), and I would need
some more detailed description on what you are trying to do with the proc if
I should try to assist you with a workaround....

-K


Mark A. Parsons Posted on 1997-12-31 01:08:37.0Z
Message-ID: <34A99ACE.33BC@compuserve.com>
Date: Tue, 30 Dec 1997 20:08:37 -0500
From: "Mark A. Parsons" <Iron_Horse@compuserve.com>
Reply-To: Iron_Horse@compuserve.com
Organization: Iron Horse, Inc.
X-Mailer: Mozilla 3.01 (Win95; I)
MIME-Version: 1.0
Subject: Re: Recursive stored procedure - help needed!
References: <34A8D1AC.AFF@ozemail.com.au>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 53
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5326
Article PK: 1081404

I don't have the System 11 manuals here at home nor do I have a SQL
server here at home ... so I can't answer the question about the scoping
of your cursors.

Is there a work around? Yep, don't use the cursor! <seg>

I personally hate cursors so I usually use 'pseudo-cursors', e.g., use a
loop similar to:


/* To grab each "my_key" for a given "parent" */

select @key = -9999 /* just make sure this is smaller */
/* than any other "my_key" in the */
/* table. */

while 1=1
begin
select @key = min(my_key)
from my_table
where parent = @parent and
my_key > @key

if @key is null
break

... do the rest of my processing
end

Obviously, how well this performs will be based on my indexes on
my_table. If I have an index on 'my_key' then this should work just
fine.

Now very efficient? Actually, I've found that this looping construct is
extremely efficient when I've got the right index in place and I'm
processing a lot of rows. Ever do a 'set statistics io on' on a cursor
method that has to process lots of rows ... in 11.0.x I've noticed that
the cursor will rescan your dataset each time from the *beginning* of
the set up to where the next fetch is to be performed. Granted, the
above loop will incur a lot of I/O's for each index tree search (once
for each row), but if the cursor is processing a *lot* of rows the above
loop comes out with fewer overall logical I/O's. Add on top of this the
problems you get with Halloween problems (often hard to eliminate in a
high transaction environment where your cursor's base table is a 'moving
target'!) .. the above loop doesn't suffer from Halloween-type problems.

The other *trick* I use is to put the desired keys into a #temp table
... this is doable in your case as long as you have a parent process
that creates the #temp table first .. then calls to your main stored
proc will simply use this #temp table as a kind-of stack. (A little
tougher to design and use than the above loop construct.)

--

Mark Parsons
Iron Horse, Inc.
[Team Sybase]