ok fixed it, no need to worry, thanks for the help.
-----Original Message----- From: Peter Harrison [mailto:[EMAIL PROTECTED]] Sent: 08 October 2002 14:04 To: [EMAIL PROTECTED] Subject: RE: [ cf-dev ] (OT) Select all records that are descendants of this record 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] -- ** 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]
