Recursive stored procedure problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Recursive stored procedure problem

Author

Topic

wind8868
Starting Member

1 Post

Posted - 2005-06-17 : 05:45:00

Hi,

I want to build a tree using a stored procedure. I have a table called GraphTreeTemp, which will be used to build the tree. The table has two columns. The first column LayerNum has 3 unique values: 1, 2, 3, which mean 1st, 2nd, and 3rd layer. The second column is NodeNum. The 1st layer has 2 nodes, the 2nd layer has 3 nodes, and the 3rd layer has 3 nodes. Hence the table looks like:

LayerNum NodeNum
1 1
1 2
2 1
2 2
2 3
3 1
3 2
3 3

I want to build a table, let's say called GraphTree. This table will have only 2 columns: NodeID and ParentNodeID.
So it will look like this:

NodeID ParentNodeID
1 ROOT NODE
2 1
3 2
4 3
5 3
6 3
7 2
8 7
9 7
10 7
11 2
12 11
13 11
14 11
15 1
......

Here is my stored procedure:

CREATE PROCEDURE sp_GraphTree
@intLayerRev int OUTPUT,
@numLayers int,
@nodeID int OUTPUT

AS
SET NOCOUNT ON

DECLARE @intLayer int
DECLARE @numNodes int
DECLARE @i int
DECLARE @parentID int
DECLARE @intTemp int
DECLARE @strTemp varchar(60)

IF @intLayerRev = 0
RETURN

SET @intLayer = @numLayers - @intLayerRev + 1
SELECT @numNodes = MAX(NodeNum) FROM GraphTreeTemp
WHERE LayerNum = @intLayer GROUP BY LayerNum

SET @i = 1

WHILE (@i <= @numNodes)
BEGIN
SET @nodeID = @nodeID + 1

IF @i < 2
SET @parentID = @nodeID - 1

SET @strTemp = STR(@nodeID, 2, 0) + ' ' + STR (@parentID, 2, 0)
PRINT @strTemp

SET @intTemp = @intLayerRev - 1

EXEC sp_GraphTree @intTemp, @numLayers, @nodeID

SET @i = @i + 1
END
GO

I didn't get the result I wanted. It seems the sp didn't get @nodeID right. Any help will be appreciated.

Wind

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-06-17 : 11:37:57

There are several articles on this site about trees and hierachies. Here is how I usually do it:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49061

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources