Building Tree View Resultset
page 2 of 3
by Ameet Phadnis
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 24685/ 43

Hierarchal data Stored procedures using Temp tables

For explaining this procedure I will use the following table








The menuSubOrder will sort the sub menus. For example, you have MenuItem1 with two sub menus called SubMenuItem1 and SubMenuItem2. The SubMenuItem2 is supposed to be before SubMenuItem1, in this scenario the MenuSubOrder for Submenuitem2 will be 1 and for SubmenuItem1 will be 2.


First, we need to create a temp table and populate it with all records. You can either use the Create Table SQL Statement or you can use the SELECT INTO construct. For this example, your structure might look like –



            MenuID int NOT NULL,

            MenuName nvarchar(50),

            ParentMenuID int NULL,

            DisplayOrder int Null,

            MenuSubOrder int Null,

            ViewLevel int Null


In this case, you will have to write separate select statement to insert all rows










            IsNull(ParentMenuID, 0),


FROM            Menus


OR use the following syntax


SELECT        MenuID,


                        IsNull(ParentMenuID, 0) ParentMenuID,

                        Null DisplayOrder,


                        Null ViewLevel

INTO               #TempMenus

FROM                        Menus


We have all the records that will be returned back. We are changing the ParentMenuID from Null to 0 for specifying the root.


We will have to declare all variables that needs to be used. The following variables will be used –


@DisplayOrder int - This variable is being used to increment the actual order to return the result set.

@MenuID int – This variable stores the Current TabID

@ParentMenuID int – This Variable stores the Parent TabID of the current Tab

@ViewLevel int – The tree view position is stored in this variable


We also initialize all the variables before using it –


SET @DisplayOrder = 0

SET @ViewLevel = 0

SET @ParentMenuID = 0

SET @ViewLevel = 0


The next step is to loop through this table to determine the relationship. To achieve this we will be using While Loop


While Exists(Select * from #TempMenus Where DisplayOrder is Null AND ParentMenuID = @ParentMenuID)


In short, this while loop will continue on till all records do have some DisplayOrder value. When we return the resultset it is going to be sorted based on the displayOrder.


Now we work with single record at a time. We are interested in getting the child record which does not have any value for DisplayOrder.


SELECT TOP 1 @MenuID = TabID FROM #TempMenus Where DisplayOrder is Null AND ParentMenuID = @ParentMenuID Order by MenuSubOrder


Next Step is to get the next displayorder and the view level


SET @DisplayOrder = @DisplayOrder + 1

SELECT @ViewLevel = ViewLevel + 1 FROM #TempTabs WHERE TabID = @ParentTabID


Once, we have the next display order and view level we update the temp table.


UPDATE #TempMenus

SET    DisplayOrder = @DisplayOrder,

            ViewLevel = @ViewLevel

WHERE         MenuID = @MenuID


Now we have the current record that was modified. So, we need to check if the current menu is parent to some other menu. We can check it as follows –


If Exists(Select * from #TempMenus Where DisplayOrder is Null AND ParentMenuID = @MenuID)


If any records were found then we use that MenuID as the PArentMenuID now

SET @ParentMenuID = @MenuID


If no child records are present for the current menuID then we should go up the tree to find the parent menu ID which might have child records that does not have display order. This is achieved by –


SET @ParentMenuID = @MenuID

While (Not Exists(Select * from #TempMenus Where DisplayOrder is Null AND ParentMenuID = @ParentMenuID)) And @parentMenuID <> 0


            SELECT @ParentMenuID = ParentMenuID FROM #TempMenus Where MenuID = @ParentMenuID


IF @ParentMenuID = 0

            SET @ViewLevel = 0


All the above steps will loop through each record and assign a value to the displayorder column.


Finally, we are going to display all the records ordered by displayorder.

SELECT * FROM #TempMenus Order by DisplayOrder


Also, as it is a temp table we need to drop that temp table

Drop Table #TempMenus.

View Entire Article

User Comments

Title: Thanks you   
Name: Xuân Dũng
Date: 2010-08-04 3:09:04 AM
Thanks you.
Excellent work
Title: Thank You!   
Name: Jenny J.
Date: 2009-06-12 11:39:35 AM
Excellent work and very useful.
Title: navigation   
Name: treeview
Date: 2009-05-19 12:13:40 PM
how to create

Community Advice: ASP | SQL | XML | Regular Expressions | Windows

©Copyright 1998-2024  |  Page Processed at 2024-02-22 3:31:26 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search