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

Complete SQL Script

CREATE PROCEDURE GetMenus

AS

--Variable Declaration.

declare @DisplayOrder int,

            @MenuID int,

            @ParentMenuID int,

            @ViewLevel int

 

-- Insert all records into the temp table.

 

SELECT        MenuID,

            MenuName,

            IsNull(ParentMenuId, 0) ParentMenuID,

            Null DisplayOrder,

            MenuOrder,

            Null ViewLevel

INTO   #TempMenus

FROM            Menus

 

-- Initalize the variables for display order, view level and the parent menu Id 

 

SET @DisplayOrder = 0

SET @ViewLevel = 0

SET @ParentMenuID = 0

 

--Loop through all menu Items and set the display order

 

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

Begin

 

-- Select the top 1 menu item which does not have display order value and is child of the current Menu.

           

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

 

-- Increment the display order. 

            SET @DisplayOrder = @DisplayOrder + 1

 

-- Get the current view level and increment it by 1.

            SELECT @ViewLevel = ViewLevel + 1 FROM #TempMenus WHERE MenuID = @ParentMenuID

 

-- Update the display order and view level in the temp table.

 

            UPDATE #TempMenus

            SET    DisplayOrder = @DisplayOrder,

                        ViewLevel = @ViewLevel

            WHERE         MenuID = @MenuID

 

-- Check if there are child menus under this child menu.

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

            Begin

 

-- If there are child menu items under the current child menu item then make the child menu item as the parent menu item.

 

                        SET @ParentMenuID = @MenuID

                       

            End

            Else

            Begin

-- This child item does not have any child menu items then go up level till you reach the top. If the top level has more child menu items then process those.

 

                        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

                                    --Print 'Inside While: ' + Cast(@ParentMenuID as varchar(4))

                        End

                        IF @ParentMenuID = 0

                                    SET @ViewLevel = 0

            End

End

 

-- Get the resultset ordered by display order.

 

SELECT * FROM #TempMenus Order by DisplayOrder

DROP Table #TempMenus

GO


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-12-05 7:03:36 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search