Hi

I've been working with a really slow cursor to traverse the adjacency
list hierarchy I'm stuck working with. Been playing round with some
code based on the books online example and have been getting the
desired results and quickly. Unfortunately the example which I cut off
this newsgroup (I can't refind the post to gather the posters name.
I'm very sorry) prints the queries results to a message instead of
selecting them in to a table. I've spent alot of time trying but can't
get the following statement to select the hierarchy results into a
temporary table so that I may base a report on it. Any pointers would
be much appreciated.

Thankyou Michael

IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'expand'
AND type = 'P')
DROP PROCEDURE expand
go
CREATE PROCEDURE expand (@current char(20)) as
SET NOCOUNT ON
DECLARE @lvl int, @line char(20)

-- Get the root node, if @current is null
if @current is null
set @current = (select distinct parent
from workorder h1
where not exists(select Wonum
from workorder h2
where h1.parent = h2.wonum))

CREATE TABLE #stack (wonum char(20), lvl int)
INSERT INTO #stack VALUES (@current, 1)
SELECT @lvl = 1
WHILE @lvl > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE lvl = @lvl)
BEGIN
SELECT @current = wonum
FROM #stack
WHERE lvl = @lvl

SELECT @line = space(@lvl - 1) + @current
Into #hi (wonum char (20), lvl int)



DELETE FROM #stack
WHERE lvl = @lvl
AND wonum = @current

INSERT #stack
SELECT wonum, @lvl + 1
FROM workorder
WHERE parent = @current

IF @@ROWCOUNT > 0
SELECT @lvl = @lvl + 1
END
ELSE
SELECT @lvl = @lvl - 1
END -- WHILE
Select * from #hi

Mike,

[quoted text, click to view]

create table Hierarchy (
Parent varchar(30) not null,
Child varchar(30) not null)
go
insert Hierarchy (Parent, Child) values ('World', 'Europe')
insert Hierarchy (Parent, Child) values ('World', 'North
America')
insert Hierarchy (Parent, Child) values ('Europe', 'France')
insert Hierarchy (Parent, Child) values ('France', 'Paris')
insert Hierarchy (Parent, Child) values ('North America', 'United
States')
insert Hierarchy (Parent, Child) values ('North America', 'Canada')
insert Hierarchy (Parent, Child) values ('United States', 'New
York')
insert Hierarchy (Parent, Child) values ('United States',
'Washington')
insert Hierarchy (Parent, Child) values ('New York', 'New York
City')
insert Hierarchy (Parent, Child) values ('Washington', 'Redmond')
go

create procedure expand (@current char(20)) as

set nocount on
declare @Level int
declare @Count int

create table #Tree (
LevelNbr int not null,
Parent varchar(20) not null,
Child varchar(20) not null,
SortSeq varchar(4000) not null
)
insert #Tree
select distinct 0, Parent, Parent, Parent
from Hierarchy
where Parent = @Current
select @Count = @@rowcount, @Level = 0

while (@Count > 0) begin
insert #Tree
select @Level + 1,
Hierarchy.Parent,
Hierarchy.Child,
#Tree.SortSeq + '.' + Hierarchy.Child
from Hierarchy
join #Tree
on #Tree.Child = Hierarchy.Parent
where #Tree.LevelNbr = @Level
select @Count = @@rowcount, @Level = @Level + 1
-- select @Count, @Level
end
select replicate (' ', Levelnbr) + Child from #Tree
order by SortSeq
drop table #Tree
go
exec expand 'World'
exec expand 'North America'
go
drop table Hierarchy
drop procedure expand