I create a web application and I need to show the navigation to the current page as breadcrumb. The structure of the menu is stored in the database and I will use a recursive CTE to show the current page as breadcrumb.



I store the menu in the database, because it is dynamic, i.e. i use a web interface to control the menu items and I show/hide menu items to a customer, based ot their permissions.

The current menu has 3 levels and its structure is:

Customers Add Contact Send Email Create Package Reports Birthdays Sales Items Add Manage Inventory Store 1 Store 2 Sale Add Reports By Period By Customer By Item 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 Customers Add Contact Send Email Create Package Reports Birthdays Sales Items Add Manage Inventory Store 1 Store 2 Sale Add Reports By Period By Customer By Item

Let’s simulate the table in which the menu is stored:

USE PL; GO DROP TABLE IF EXISTS #Menu; CREATE TABLE #Menu ( PageID INT , ParentPageID INT , [Name] NVARCHAR(128) ); INSERT #Menu (ParentPageID, PageID, [Name]) SELECT NULL, 1, N'Customers' UNION ALL SELECT NULL, 2, N'Items' UNION ALL SELECT NULL, 3, N'Sales' UNION ALL SELECT 1, 4, N'Add' UNION ALL SELECT 1, 5, N'Contact' UNION ALL SELECT 1, 6, N'Reports' UNION ALL SELECT 2, 7, N'Add' UNION ALL SELECT 2, 8, N'Manage Inventory' UNION ALL SELECT 3, 9, N'Add' UNION ALL SELECT 3, 10, N'Reports' UNION ALL SELECT 5, 11, N'Send Email' UNION ALL SELECT 5, 12, N'Create Package' UNION ALL SELECT 6, 13, N'Birthdays' UNION ALL SELECT 6, 14, N'Sales' UNION ALL SELECT 8, 15, N'Store 1' UNION ALL SELECT 8, 16, N'Store 2' UNION ALL SELECT 10, 17, N'By Period' UNION ALL SELECT 10, 18, N'By Customer' UNION ALL SELECT 10, 19, N'By Item'; SELECT * FROM #Menu; GO 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 USE PL ; GO DROP TABLE IF EXISTS # Menu ; CREATE TABLE # Menu ( PageID INT , ParentPageID INT , [ Name ] NVARCHAR ( 128 ) ) ; INSERT # Menu ( ParentPageID , PageID , [ Name ] ) SELECT NULL , 1 , N 'Customers' UNION ALL SELECT NULL , 2 , N 'Items' UNION ALL SELECT NULL , 3 , N 'Sales' UNION ALL SELECT 1 , 4 , N 'Add' UNION ALL SELECT 1 , 5 , N 'Contact' UNION ALL SELECT 1 , 6 , N 'Reports' UNION ALL SELECT 2 , 7 , N 'Add' UNION ALL SELECT 2 , 8 , N 'Manage Inventory' UNION ALL SELECT 3 , 9 , N 'Add' UNION ALL SELECT 3 , 10 , N 'Reports' UNION ALL SELECT 5 , 11 , N 'Send Email' UNION ALL SELECT 5 , 12 , N 'Create Package' UNION ALL SELECT 6 , 13 , N 'Birthdays' UNION ALL SELECT 6 , 14 , N 'Sales' UNION ALL SELECT 8 , 15 , N 'Store 1' UNION ALL SELECT 8 , 16 , N 'Store 2' UNION ALL SELECT 10 , 17 , N 'By Period' UNION ALL SELECT 10 , 18 , N 'By Customer' UNION ALL SELECT 10 , 19 , N 'By Item' ; SELECT * FROM # Menu ; GO

I use a Parent – Child hierarchy to create a multi-level menu.

The menu with the breadcrumbs:

WITH CTE_Page AS -- Parent to child ( SELECT P1.ParentPageID , P1.PageID , 0 AS [Level] , P1.[Name] , CAST(P1.[Name] AS NVARCHAR(MAX)) AS Breadcrumb FROM #Menu AS P1 WHERE P1.ParentPageID IS NULL UNION ALL SELECT P2.ParentPageID , P2.PageID , C.[Level] + 1 , P2.[Name] , CONCAT(CAST(C.Breadcrumb AS NVARCHAR(MAX)), N' > ', CAST(P2.[Name] AS NVARCHAR(MAX))) FROM #Menu AS P2 JOIN CTE_Page AS C ON P2.ParentPageID = C.PageID ) SELECT * FROM CTE_Page ORDER BY ParentPageID GO 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 WITH CTE_Page AS -- Parent to child ( SELECT P1 . ParentPageID , P1 . PageID , 0 AS [ Level ] , P1 . [ Name ] , CAST ( P1 . [ Name ] AS NVARCHAR ( MAX ) ) AS Breadcrumb FROM # Menu AS P1 WHERE P1 . ParentPageID IS NULL UNION ALL SELECT P2 . ParentPageID , P2 . PageID , C . [ Level ] + 1 , P2 . [ Name ] , CONCAT ( CAST ( C . Breadcrumb AS NVARCHAR ( MAX ) ) , N ' > ' , CAST ( P2 . [ Name ] AS NVARCHAR ( MAX ) ) ) FROM # Menu AS P2 JOIN CTE_Page AS C ON P2 . ParentPageID = C . PageID ) SELECT * FROM CTE_Page ORDER BY ParentPageID GO

The above query is parent to child recursive CTE. I need to show the breadcrumb for a specified PageID, i.e. I need to build the breadcrumb as child to parent.

To translate PageID 17 to breadcrumb, I wrote recursive CTE with child to parent logic:

DECLARE @PageID INT = 17; WITH CTE_Menu AS -- Child to Parent ( SELECT P1.ParentPageID , P1.PageID , CAST([Name] AS NVARCHAR(MAX)) AS Breadcrumb FROM #Menu AS P1 WHERE PageID = @PageID UNION ALL SELECT P2.ParentPageID , C.PageID , CONCAT(CAST(P2.[Name] AS NVARCHAR(MAX)), N' > ', CAST(C.Breadcrumb AS NVARCHAR(MAX))) FROM #Menu AS P2 JOIN CTE_Menu AS C ON C.ParentPageID = P2.PageID ) SELECT Breadcrumb FROM CTE_Menu WHERE ParentPageID IS NULL; 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 DECLARE @ PageID INT = 17 ; WITH CTE_Menu AS -- Child to Parent ( SELECT P1 . ParentPageID , P1 . PageID , CAST ( [ Name ] AS NVARCHAR ( MAX ) ) AS Breadcrumb FROM # Menu AS P1 WHERE PageID = @ PageID UNION ALL SELECT P2 . ParentPageID , C . PageID , CONCAT ( CAST ( P2 . [ Name ] AS NVARCHAR ( MAX ) ) , N ' > ' , CAST ( C . Breadcrumb AS NVARCHAR ( MAX ) ) ) FROM # Menu AS P2 JOIN CTE_Menu AS C ON C . ParentPageID = P2 . PageID ) SELECT Breadcrumb FROM CTE_Menu WHERE ParentPageID IS NULL ;

Keep it simple :-)