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

Hierarchal data Stored procedures using Temp tables

For explaining this procedure I will use the following table

 

Menus

MenuID

MenuName

ParentMenuID

MenuSubOrder

 

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 –

CREATE TABLE #TempMenus

(

            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

INSERT INTO #TempMenus

(

            MenuID,

            MenuName,

            ParentMenuID,

            MenuSubOrder

)

SELECT MenuID,

            MenuName,

            IsNull(ParentMenuID, 0),

            MenuSubOrder

FROM            Menus

 

OR use the following syntax

 

SELECT        MenuID,

                        MenuName,

                        IsNull(ParentMenuID, 0) ParentMenuID,

                        Null DisplayOrder,

                        MenuSubOrder,

                        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

Begin

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

End

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
Comment:
Thanks you.
Excellent work
Title: Thank You!   
Name: Jenny J.
Date: 2009-06-12 11:39:35 AM
Comment:
Excellent work and very useful.
Title: navigation   
Name: treeview
Date: 2009-05-19 12:13:40 PM
Comment:
how to create






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


©Copyright 1998-2021 ASPAlliance.com  |  Page Processed at 2021-02-27 10:25:40 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search