Creating a resource tree.

[jflachman]

I need to create a resource tree / org chart.
I have a table
[tt]
Resources
---------------------------------------------------
ResID Name MgrID Layer TreeID
1 Bill 2
2 Paul 0
3 Mary 4
4 John 2
5 Thom 2
6 Carl 4
--------------------------------------------------
[/tt]
I need to populate the Layer and TreeID fields such that I can get this output
[tt]
---------------------------------------------------
ResID Name MgrID Layer TreeID
2 Paul 0 1 1
1 Bill 2 2 2
4 John 2 2 3
6 Carl 4 3 4
3 Mary 4 3 5
5 Thom 2 2 6
--------------------------------------------------
[/tt]
I am doing this in MS Access ADP and SQL Server 2000. I am pretty decent at Access MDBs but I am very new to ADPs, SQL Server 2000 and Transact-SQL.

In the MDB version of this database, I used a short bit of recursive code. It was slow, but it worked.

I could probably do this with cursors in a stored procedures but everyone says "cursors are bad" and I have a problem with the recursive call to an SP with cursors because cursors can't be reallocated until they are deallocated.

How else might I do this on the server side?

Thanks,


Jonathan
________________________________________
It is not fair to ask of others what you are unwilling to do yourself.
-Eleanor Roosevelt

 

[jflachman]

I clearly didn't provide sufficient information to make this easy to answer. I'll try again.

The Layer field is the number of layers down in the resource tree. i.e. Paul is the boss, so he is Layer 1 Bill, John and Thom report to Paul, so they are Layer 2, etc.

The TreeID field is required to get the sort in the right order. If I only have Layer, I get the CEO, then all the VPs, then all the Directors. What I need is the CEO, then one VP, their first director, his reports, the next director, her reports, then the next VP, etc.

I started out with these two Stored Procedures:

ALTER PROCEDURE CreateResourceTree
AS
SET NOCOUNT ON 

DECLARE @Layer int, @TreeID int, @ResourceID int, @ManagerID int
SET @Layer = 1
SET @TreeID = 1

DECLARE csrResource CURSOR FOR 
SELECT ResourceID, ManagerID FROM Resources WHERE (ManagerID = 0) ORDER BY FirstName, LastName
OPEN csrResource
FETCH NEXT from csrResource INTO @ResourceID, @ManagerID

WHILE @@FETCH_STATUS = 0
	BEGIN
	UPDATE Resources SET TreeID = @TreeID, Layer = @Layer WHERE ResourceID = @ResourceID
	EXEC CreateResourceTreeNode @Layer, @TreeID OUTPUT, @ResourceID
	FETCH NEXT from csrResource INTO @ResourceID, @ManagerID
	SET @TreeID = @TreeID + 1
	END

CLOSE csrResource
DEALLOCATE csrResource
RETURN 

ALTER PROCEDURE CreateResourceTreeNode 
	(
	@Layer int = NULL,
	@TreeID	int = NULL OUTPUT,
	@ParentID int = NULL
	)

AS
SET NOCOUNT ON
DECLARE @ResourceID int, @ManagerID int

DECLARE csrResourceNode CURSOR FOR 
SELECT ResourceID, ManagerID FROM Resources WHERE (ManagerID = @ParentID) ORDER BY FirstName, LastName
OPEN csrResourceNode
FETCH NEXT from csrResourceNode INTO @ResourceID, @ManagerID
SET @Layer = @Layer+1
WHILE @@FETCH_STATUS = 0
	BEGIN
	UPDATE Resources SET TreeID = @TreeID, Layer = @Layer WHERE ResourceID = @ResourceID
	SET @TreeID = @TreeID + 1
	EXEC CreateResourceTreeNode @Layer, @TreeID OUTPUT, @ResourceID
	FETCH NEXT from csrResourceNode INTO @ResourceID, @ManagerID
	END

CLOSE csrResourceNode
DEALLOCATE csrResourceNode

RETURN

I ran into trouble with the recursive call to CreateResourceTreeNode. I get an error that says "A cursor with the name 'csrResourceNode' already exists"

I'm not sure the code does exactly what I need it to do, but I can make that work if I can get around the recursion problem.
Any help?

Jonathan
________________________________________
It is not fair to ask of others what you are unwilling to do yourself.
-Eleanor Roosevelt