Click here to Skip to main content
12,452,215 members (54,952 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


11 bookmarked

Recursive Queries in Microsoft SQL Server 2008

, 10 Nov 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
Recursive queries in Microsoft SQL Server 2008

Case 1

In human resource management system, each employee is supervised by another employee. To do this, I create a table where I assigned one employee with another in the following way:

Employee ID Name Supervisor ID
001 Paroar Habib Null
002 Sumon Mazumder 001
003 Kalim Uddin 001
004 Jewel Ahmed 002

Case 2

In the same human resource management system, I have to create a dynamic menu to give permission to an individual user. For that reason, I create a table as follows:

vmenuid vmenuname vparent
M001 HR System Null
M002 Payroll M001
M003 Salary Benefits M002
M004 Recruitment M001

In both the cases, there is a recursive situation that happens in both tables. So if I want to create a query to get all the menu details with parent id and steps, then I can write the following queries which are basically called Recursive Queries Using Common Table Expressions.

WITH security_menu_Recursive(Parent,MenuId,MenuName,LEVEL)
    SELECT vparent,vmenuid,vmenuname,0 AS LEVEL FROM dbo.SecurityMenu WHERE vParent = null
    SELECT vparent,vmenuid,vmenuname,Level + 1 AS LEVEL FROM dbo.SecurityMenu
    INNER JOIN security_menu_Recursive AS smr ON smr.menuid = dbo.SecurityMenu.vParent
SELECT parent,menuid,menuname,LEVEL FROM security_menu_Recursive

After executing the code, I got the below data:

Parent MenuID MenuName Level
Null M001 HR System 0
M001 M002 Payroll 1
M001 M004 Recruitment 1
M002 M003 Salary Benefits 2

If you know anything easier or any alternatives, then please give your best comments.

The post Recursive queries in Microsoft SQL Server 2008 appeared first on crea8ivecode.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Sadeque Sharif
Software Developer Meridian Group
Bangladesh Bangladesh
I am:
Manager (IT) at Meridian Group.

I was:
Assistant Manager (Software Division) at KDS Garment Industries Limited.
Assistant Manager (Software Division) at E-Vision Software Limited.

My blog:

My preferred work area:

My email:

Follow me:
twitter | facebook | linkedin

You may also be interested in...


Comments and Discussions

QuestionThis query doesn't work Pin
Dilshods8-Jan-16 21:06
memberDilshods8-Jan-16 21:06 
Questionmenu hierarchy name Pin
kepulo15-Nov-15 0:26
memberkepulo15-Nov-15 0:26 
QuestionIs this query work? Pin
Thava Rajan29-Oct-13 19:26
memberThava Rajan29-Oct-13 19:26 
AnswerRe: Is this query work? Pin
Sadeque Sharif29-Oct-13 21:02
memberSadeque Sharif29-Oct-13 21:02 
GeneralRe: Is this query work? Pin
Thava Rajan30-Oct-13 3:37
memberThava Rajan30-Oct-13 3:37 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.160826.1 | Last Updated 10 Nov 2013
Article Copyright 2013 by Sadeque Sharif
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid