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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-06-12 : 22:30:59
|
Brent writes "Here is a question that I have not been able to solve. I'm attempting to display parts of an organizational tree structure stored in a MS SQL DB. I have written a store procedure to receive a given node ID (@NodeID) and output all children below it.
The code below works fine, but I need to be able to make it work with a dynamically supplied @TableID in place of the hard-code table name TreeData_76 in the nested insert/select statement below. i.e. Substitute @TableID in place of TreeData_76.
The table TreeData_76 contains a list of node IDs and associated parent nodes.
NodeID, ParentID
1, 0 -- root
2, 1
3, 1
4, 2 etc...
@rank determines how many levels deep the children are from the supplied @NodeID. A temporary table is used to hold and then return the result.
Thanks in advance for any advice.
Brent
CREATE PROCEDURE [dbo].[dmsp_GetTreeListAfter]
@TableID int
,@NodeID int
AS
DECLARE @txt_TableID varchar(5)
DECLARE @txt_NodeID varchar(10)
DECLARE @rank int
SET @txt_TableID=CONVERT(VarChar(5), @TableID)
SET @txt_NodeID = CONVERT( varchar(10), @NodeID)
select @rank = 0
create table #table1 (id int, rank int)
insert into #table1 (id, rank) values (@NodeID, @rank)
#table1.rank = @rank - 1"
while (@@rowcount > 0)
begin
select @rank = @rank + 1
insert into #table1 (id, rank)
select TreeData_76.NodeID, @rank FROM TreeData_76
inner join #table1 on TreeData_76.ParentID = #table1.id where #table1.rank = @rank - 1
end
/* Display the temp table contain all the childen of the */
/* supplied node */
select * from #table1
RETURN 0
GO" |
|
|
|
|
|
|
|
SQL Tree (using dynamic SQL)