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

3 posts in Product Futures Discussion Last posting was on 2002-08-16 14:31:53.0Z
Anders_P Posted on 2002-08-16 10:11:02.0Z
From: Anders_P
Date: Fri, 16 Aug 2002 06:11:02 -0400
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Recursive queries / procedures
Message-ID: <E684B3BECB7C825C0037F13A85256C17.0037F15C85256C17@webforums>
Lines: 56
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:319
Article PK: 93488

Is it possible to use recursive queries in Sybase (compare START
WITH/CONNECT BY in Oracle)? I can't find it in the manuals. The alternative
seems to be nested procedures??

I have tried to get it to work in the following stored procedure but it
seems like the temporary table can not be found when NESTLEVEL is greater
than 1.

CREATE PROCEDURE sp_Recurse
(
@TopID int
)
AS
SET NOCOUNT ON

IF (@@NESTLEVEL = 1)
BEGIN
IF EXISTS(SELECT name FROM tempdb..sysobjects WHERE name like
'#RecurseTemp')
DROP TABLE #RecurseTemp
CREATE TABLE #RecurseTemp (
ItemID int NULL,
ItemName varchar (50) NULL,
NestLevel int
)
END
select 'Before INSERT'
INSERT INTO #RecurseTemp SELECT I.*, @@NESTLEVEL FROM tblItem I INNER JOIN
tblTree T ON T.ItemID = I.ItemID WHERE T.TreeID = @TopID
select 'After INSERT'
if @@error != 0
BEGIN
SELECT 'Felkod='+convert(varchar(10),@@error)
END

DECLARE @ChildID int

DECLARE curRecurse CURSOR FOR SELECT TreeID FROM tblTree WHERE ParentTreeID
= @TopID
OPEN curRecurse
FETCH curRecurse INTO @ChildID
WHILE (@@sqlstatus = 0)
BEGIN
SELECT "EXECUTE sp_Recurse"
EXEC sp_Recurse @ChildID
FETCH curRecurse INTO @ChildID
END
CLOSE curRecurse
DEALLOCATE cursor curRecurse

IF (@@NESTLEVEL = 1)
BEGIN
SELECT * FROM #RecurseTemp
DROP TABLE #RecurseTemp
END

RETURN


Bret Halford Posted on 2002-08-16 13:38:34.0Z
Message-ID: <3D5D005A.496E8D1B@sybase.com>
Date: Fri, 16 Aug 2002 07:38:34 -0600
From: Bret Halford <bret@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.76 [en] (Windows NT 5.0; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Recursive queries / procedures
References: <E684B3BECB7C825C0037F13A85256C17.0037F15C85256C17@webforums>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 48
NNTP-Posting-Host: 10.22.120.76
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:318
Article PK: 93487

ASE doesn't have any specific built-in operators for this kind of query.
There is an open feature request for such operators, CR 65795.

A technique for doing them with the existing commands is discussed at
http://www.isug.com/Sybase_FAQ/ASE/section6.2.html#6.2.7

-bret

Anders_P wrote:
>
> Is it possible to use recursive queries in Sybase (compare START
> WITH/CONNECT BY in Oracle)? I can't find it in the manuals. The alternative
> seems to be nested procedures??
>
> I have tried to get it to work in the following stored procedure but it
> seems like the temporary table can not be found when NESTLEVEL is greater
> than 1.
>
> CREATE PROCEDURE sp_Recurse
> (
> @TopID int
> )
> AS
> SET NOCOUNT ON
>
> IF (@@NESTLEVEL = 1)
> BEGIN
> IF EXISTS(SELECT name FROM tempdb..sysobjects WHERE name like
> '#RecurseTemp')
> DROP TABLE #RecurseTemp
> CREATE TABLE #RecurseTemp (
> ItemID int NULL,
> ItemName varchar (50) NULL,
> NestLevel int
> )
> END
> select 'Before INSERT'
> INSERT INTO #RecurseTemp SELECT I.*, @@NESTLEVEL FROM tblItem I INNER JOIN
> tblTree T ON T.ItemID = I.ItemID WHERE T.TreeID = @TopID
> select 'After INSERT'
> if @@error != 0
> BEGIN
> SELECT 'Felkod='+convert(varchar(10),@@error)
> END
>
> DECLARE @ChildID int
>
> DECLARE curRecurse CURSOR FOR SELECT TreeID FROM tblTree WHERE ParentTreeID
> = @TopID
> OPEN curRecurse
> FETCH curRecurse INTO @ChildID
> WHILE (@@sqlstatus = 0)
> BEGIN
> SELECT "EXECUTE sp_Recurse"
> EXEC sp_Recurse @ChildID
> FETCH curRecurse INTO @ChildID
> END
> CLOSE curRecurse
> DEALLOCATE cursor curRecurse
>
> IF (@@NESTLEVEL = 1)
> BEGIN
> SELECT * FROM #RecurseTemp
> DROP TABLE #RecurseTemp
> END
>
> RETURN


Matt Rogish Posted on 2002-08-16 14:31:53.0Z
From: "Matt Rogish" <matt@fanhome.com>
References: <E684B3BECB7C825C0037F13A85256C17.0037F15C85256C17@webforums>
Subject: Re: Recursive queries / procedures
Date: Fri, 16 Aug 2002 10:31:53 -0400
Lines: 44
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <xuIMZETRCHA.245@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: dhcp16464033.woh.rr.com 24.164.64.33
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:317
Article PK: 93494

UGH! Cursors! Eeeviill! :holds fingers in the sign of a cross:

Ok, here is what I do to get the parent and child nodes of a particular
tree. Let's say my table structure looks like this (for simplicity):
node( node_id, parent_id )

So for node X I would like to get all the children.

CREATE PROCEDURE dbo.get_parent_and_children
@parent_id INT
AS
CREATE TABLE #node_ids( node_id INT NOT NULL )

INSERT INTO #node_ids VALUES( @parent_id )

WHILE @@rowcount > 0
INSERT INTO #node_ids
SELECT n.node_id
FROM n.node,
#node_ids ni
WHERE ni.node_id = n.parentid
AND NOT EXISTS( SELECT *
FROM #node_ids ni2
WHERE ni.node_id = ni2.node_id )

END

SELECT n.*
FROM node n
INNER JOIN #node_ids ni ON n.node_id = ni.node_id
GO

--
Matt

<Anders_P> wrote in message
news:E684B3BECB7C825C0037F13A85256C17.0037F15C85256C17@webforums...
> Is it possible to use recursive queries in Sybase (compare START
> WITH/CONNECT BY in Oracle)? I can't find it in the manuals. The
alternative
> seems to be nested procedures??