Creating a Recursive Menu in SQL Server

Hello everyone. In this article, I will try to give information about creating a Recursive Menu in SQL Server.

You can create simple or advanced nested menus in SQL Server.

First, let’s create a table called “Menu”.

CREATE TABLE Menu (
	MenuID INT PRIMARY KEY
   ,MenuName NVARCHAR(50)
   ,ParentMenuID INT NULL
   ,FOREIGN KEY (ParentMenuID) REFERENCES Menu (MenuID)
);

Next, let’s add at least 10 menu and submenu contents to the table.

INSERT INTO Menu (MenuID, MenuName, ParentMenuID)
	VALUES (1, 'Ana Menü 1', NULL),
	(2, 'Ana Menü 2', NULL),
	(3, 'Alt Menü 1.1', 1),
	(4, 'Alt Menü 1.2', 1),
	(5, 'Alt Menü 1.3', 1),
	(6, 'Alt Menü 2.1', 2),
	(7, 'Alt Menü 2.2', 2),
	(8, 'Alt Menü 2.3', 2),
	(9, 'Alt Menü 2.4', 2),
	(10, 'Alt Menü 2.5', 2);

In this example, the “MenuID” column is used as a unique menu ID. The “MenuName” column contains the menu name. The “ParentMenuID” column is used to link a submenu to the main menu. The “ParentMenuID” value of the submenus must be equal to the “MenuID” value of the relevant main menu. The “ParentMenuID” value of the main menus is set to NULL.

In SQL Server we can use a recursive query to associate main menus with submenus in a table.

The following example shows a table hierarchically where the main menu and submenus are associated.

WITH RecursiveMenu
AS
(SELECT
		MenuID
	   ,MenuName
	   ,ParentMenuID
	   ,MenuLevel = 0
	   ,MenuPath = CAST(MenuName AS NVARCHAR(MAX))
	FROM Menu
	WHERE ParentMenuID IS NULL

	UNION ALL

	SELECT
		m.MenuID
	   ,m.MenuName
	   ,m.ParentMenuID
	   ,MenuLevel = rm.MenuLevel + 1
	   ,MenuPath = CAST(rm.MenuPath + ' > ' + m.MenuName AS NVARCHAR(MAX))
	FROM Menu AS m
	INNER JOIN RecursiveMenu AS rm
		ON m.ParentMenuID = rm.MenuID)
SELECT
	MenuID
   ,MenuName
   ,MenuLevel
   ,MenuPath
FROM RecursiveMenu
ORDER BY MenuPath;

Yukarıdaki sorguyu çalıştırınca aşağıdakine benzer bir sonuç göreceksiniz.

As you can see, we have created a simple Recursive menu.

Good luck to everyone in business and life.