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
