Tree / Hierarchy Stored Procedure Help

[kloner]

Hi all,

I am getting close to solving my problem, however some extra help would be great as I am now stumped!

I have a table that contains 'Sections'. There is a root section and many children, which can have more children etc. As you can see from my tree result below, the output is looking ok.



----Girlguiding
--------Girls and Young Women
------------Rainbows
------------Brownies
------------Guides
------------Senior Section
----------------About Senior Section
--------------------4ward, 4self, 4others
--------------------LINK
--------------------Young Guiders
--------------------Trefoil Guild
--------------------Lones
--------------------Rangers
--------------------Young Leaders
--------------------Duke of Edinburgh Award Scheme
--------------------SSAGO
--------------------Look Wider Group
----------------What can I do?
--------------------Look Wider
------------------------Programme
------------------------Case Studies
------------------------FAQ
--------------------Awards and Qualifications
--------------------Other Opportunities
----------------Guiders and Parents
----------------News
----------------Innovate
----------------Resources and Links
----------------FAQ's
----------------Get Interactive
--------Football Festival
------------When and where?
------------News and what's on?
------------Register your team
------------Before the event
------------Football training


My problem is, I am storing this information in a temp table, and it doesn't like doing UPDATE statements for some reason. I have no idea why.

I am also trying to record the 'level' of each section. For example level 0 would be the root, and 'Brownies' would be 2. Any ideas?

My table setup is as follows:

TBL_SECTIONS
------------
[ID]
[ParentID]
[SectionName]



My Stored Procedure / Query Analyser details are
=================================================

-- Clear old data and recreate SP.

DROP TABLE #TempTreeTable;
DROP PROC dbo.sps_ShowSectionHierarchy;


-- Run SP Create
CREATE PROC dbo.sps_ShowSectionHierarchy ( @Root int ) AS
BEGIN
	
	-- How do I do a Level count?

	BEGIN TRANSACTION A	

	SET NOCOUNT ON
	DECLARE @ID int, @SectionName varchar(100)
	DECLARE @SectionNameToPrint varchar (100)
	DECLARE @PARENTID int
	DECLARE @CHILDID int

	--Create a tempory stack.	
	SET @SectionName = (SELECT SectionName FROM dbo.TBL_Sections WHERE ID = @Root)

	-- SET SECTION NAME HERE --
	--PRINT REPLICATE('-', @@NESTLEVEL * 2) + @SectionName
	SET @SectionNameToPrint = REPLICATE('--', @@NESTLEVEL * 2) + @SectionName
	PRINT @SectionNameToPrint

	-- SET PARENTID HERE
	SET @ID = (SELECT MIN(ID) FROM dbo.TBL_Sections WHERE ParentID = @Root)
	
	SET @PARENTID = @ID
	PRINT 'PARENTID: ' + CONVERT(varchar(30), @PARENTID)

	-- temp table insert
	INSERT INTO #TempTreeTable (MainID, ParentID, TempSectionName) VALUES (@Root, @ID, @SectionNameToPrint)


	WHILE @ID IS NOT NULL
	BEGIN
		EXEC dbo.sps_ShowSectionHierarchy @ID

		SET @ID = (SELECT MIN(ID) FROM dbo.tbl_sections WHERE ParentID = @Root AND ID > @ID)
		
		PRINT 'DEBUG CHILDID: ' + CONVERT(varchar(30), @ID)
		PRINT 'DEBUG ***PARENTID***: ' + CONVERT(varchar(30), @PARENTID)

		-- Why doesn't this update??
		UPDATE #TempTreeTable SET ParentID = @ParentID WHERE (MainID = @ID);
		
	END

	COMMIT TRANSACTION A

END
GO



-- Show results
CREATE TABLE #TempTreeTable (MainID int, ParentID int, TempSectionName varchar(100));
EXEC dbo.sps_ShowSectionHierarchy 1;
SELECT * FROM #TempTreeTable;

====================================================

I am also having trouble inserting the correct ParentID for each row, that is why I am doing the 'UPDATE' statement which is not working.

Any ideas or tips to help me learn would be great.

Kind regards,

Matt

 

[JohnDTampaBay]

Here's a great article from SQL Server Magazines that discusses hierarchies in detail. There is a bit of work involved to set up the table and triggers to maintain the hierarchy. In the end, it is worth it to be able to effectively query the hierarchial data. Good luck!

http://www.sqlmag.com/Articles/Print.cfm?ArticleID=8826

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.