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.
