Hi
Thanks Adrian, it's helped a lot. I am having problems returning the result
though. Here's what I have so far:
Use Playground
If Exists (Select Name From Sysobjects
Where Name = 'Expand' And Type = 'P')
Drop Procedure Expand
Go
Create Procedure Expand (@Current Char(20), @Return Varchar Output) As
Set Nocount On
Declare @Level Int, @Line Char(20)
Create Table #Stack (Item Char(20), Level Int)
Insert Into #Stack Values (@Current, 1)
Select @Level = 1
Set @Return = ''
While @Level > 0
Begin
If Exists (Select * From #Stack Where Level = @Level)
Begin
Select @Current = Item
From #Stack
Where Level = @Level
/*
Select @Line = Space(@Level - 1) + @Current
Print @Line
*/
Set @Return = @Return + ',' + @Current
Delete From #Stack
Where Level = @Level
And Item = @Current
Insert #Stack
Select Collection_Id, @Level + 1
From Tbl_Collection
Where Parent_Collection_Id = @Current
If @@Rowcount > 0
Select @Level = @Level + 1
End
Else
Select @Level = @Level - 1
End
-- While
Go
Declare @Test Varchar
Exec Expand 1, @Test
Select @Test As TheID
(the final select is returning Null, but if I uncomment the "Print" bit it
shows the correct data in the Messages box of Query Analyzer) What am I
doing wrong?
- Peter
-----Original Message-----
From: Adrian Marshall [mailto:[EMAIL PROTECTED]]
Sent: 08 October 2002 11:12
To: [EMAIL PROTECTED]
Subject: Re: [ cf-dev ] (OT) Select all records that are descendants of
this record
In SQL Server 7 there's an example of expanding hierarchies (under expanding
hierarchies in the index) in books online that's probably in the 2000 docs
too. It might need modifying a bit to do exactly what you want.
Adrian
_____________________________________________
adrian marshall
lead hat limited
28-29 great sutton street . london . ec1v 0ds
http://www.leadhat.co.uk/
e: [EMAIL PROTECTED]
p: 020 7566 9450
f: 020 7566 9458
----- Original Message -----
From: "Peter Harrison" <[EMAIL PROTECTED]>
To: "Dev@Lists. Cfdeveloper. Co. Uk" <[EMAIL PROTECTED]>
Sent: Tuesday, October 08, 2002 9:44 AM
Subject: [ cf-dev ] (OT) Select all records that are descendants of this
record
> Hi All
>
> I am trying to do a query on SQL Server 2000. I have a table with two
> fields: MYTABLE_ID and PARENT_MYTABLE_ID (relates to self to create a
> parent-child structure within the data).
>
> Example data:
>
> MYTABLE_ID,PARENT_MYTABLE_ID,[Comment]
>
> 1,NULL,(top-level - has no parent)
> 2,1
> 3,1
> 4,NULL
> 5,4
> 6,2
> 7,2
> 8,6
> 9,6
>
> How can I do a SELECT on the table to return all the descendants for a
given
> ID?
>
> Given the above example data, if I want to find all descendant records for
> MYTABLE_ID=1, it should return ID's: 2,3,6,7,8,9
>
> I don't mind using a stored procedure if I have to. The numbers of records
> will always be small, so performance is not a factor here.
>
> Oh how I miss Oracle at times like these. Thanks for any help.
>
> - Peter Harrison
>
>
> --
> ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
>
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
> For human help, e-mail: [EMAIL PROTECTED]
>
--
** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
For human help, e-mail: [EMAIL PROTECTED]
--
** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
For human help, e-mail: [EMAIL PROTECTED]
