Recursive function for get all emp under one manag

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Recursive function for get all emp under one manag

Author

Topic

sachingovekar
Posting Yak Master

101 Posts

Posted - 2010-01-11 : 01:13:52

Hi,

How to build a recursive function to get all employeess under a manager up to n levels.

CREATE TABLE dbo.emp
(
EmpID int PRIMARY KEY,
EmpName varchar(30),
MgrID int FOREIGN KEY REFERENCES Emp(EmpID)
)
GO

CREATE NONCLUSTERED INDEX NC_NU_Emp_MgrID ON dbo.emp(MgrID)
GO

INSERT dbo.emp SELECT 1, 'President', NULL
INSERT dbo.emp SELECT 2, 'Vice President', 1
INSERT dbo.emp SELECT 3, 'CEO', 2
INSERT dbo.emp SELECT 4, 'CTO', 2
INSERT dbo.emp SELECT 5, 'Group Project Manager', 4
INSERT dbo.emp SELECT 6, 'Project Manager 1', 5
INSERT dbo.emp SELECT 7, 'Project Manager 2', 5
INSERT dbo.emp SELECT 8, 'Team Leader 1', 6
INSERT dbo.emp SELECT 9, 'Software Engineer 1', 8
INSERT dbo.emp SELECT 10, 'Software Engineer 2', 8
INSERT dbo.emp SELECT 11, 'Test Lead 1', 6
INSERT dbo.emp SELECT 12, 'Tester 1', 11
INSERT dbo.emp SELECT 13, 'Tester 2', 11
INSERT dbo.emp SELECT 14, 'Team Leader 2', 7
INSERT dbo.emp SELECT 15, 'Software Engineer 3', 14
INSERT dbo.emp SELECT 16, 'Software Engineer 4', 14
INSERT dbo.emp SELECT 17, 'Test Lead 2', 7
INSERT dbo.emp SELECT 18, 'Tester 3', 17
INSERT dbo.emp SELECT 19, 'Tester 4', 17
INSERT dbo.emp SELECT 20, 'Tester 5', 17
GO

Kindli help.

Regards
Sachin

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-11 : 06:23:05

I found a very simple example that is fitting to your needs:
http://vyaskn.tripod.com/hierarchies_in_sql_server_databases.htm

No, you're never too old to Yak'n'Roll if you're too young to die.

sachingovekar
Posting Yak Master

101 Posts

Posted - 2010-01-11 : 07:25:20

when I try on this table, the logic doesn't work.

insert into dbo.emp values ('495517','Anthony Monaco','237418')
insert into dbo.emp values ('274017','Jennifer Postighone','495517')
insert into dbo.emp values ('273968','Joanne Rice','495517')
insert into dbo.emp values ('273859','Mark Hellman','495517')
insert into dbo.emp values ('273860','Hector Montoya','495517')
insert into dbo.emp values ('273967','Arlene Buhse','495517')
insert into dbo.emp values ('294086','Daniel Birdsall','495517')
insert into dbo.emp values ('314658','Emmanuel Olorunnisola','495517')
insert into dbo.emp values ('363234','Annie Phillip','495517')
insert into dbo.emp values ('363210','Douglas Kaufman','495517')
insert into dbo.emp values ('378754','David Herrick','495517')
insert into dbo.emp values ('47862296','Jerrel Hampton','495517')
insert into dbo.emp values ('362014','John Messina','274017')
insert into dbo.emp values ('361957','Robert Chielli','274017')
insert into dbo.emp values ('361958','William Rella','274017')
insert into dbo.emp values ('361989','Eric Oldmixon','274017')
insert into dbo.emp values ('525271','Winston Willocks','274017')
insert into dbo.emp values ('525273','Anne Fisher-Bara','274017')
insert into dbo.emp values ('525252','David Leung','274017')
insert into dbo.emp values ('525253','Ndolu Young','274017')
insert into dbo.emp values ('525254','Oscar Cruz','274017')
insert into dbo.emp values ('525257','Maxim Kibatullin','274017')
insert into dbo.emp values ('525230','Sylwester Maciejewski','274017')
insert into dbo.emp values ('525299','Vincent Hendricks','274017')
insert into dbo.emp values ('525300','Melvin Ramsey','274017')
insert into dbo.emp values ('525302','Alex Faylond','274017')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-11 : 11:52:20

[code]
declare @MgrID
set @MgrID=2 --sample value
declare @t table (parent varchar(20), child varchar(20), lev int, fullpath varchar(1000))
declare @lev int
set @lev = 0

--Get Root node(s)
insert @t (parent, child, lev, fullpath)
select distinct null, p.EmpID, @lev, p.EmpID
from dbo.emp p
where EmpID=@MgrID

while @@rowcount > 0
begin
set @lev = @lev + 1

--Get all children of current level
insert @t (parent, child, lev, fullpath)
select h.MgrID, h.EmpID, @lev, t.fullpath + '.' + h.EmpID
from @t t
join emp h on h.MgrID = t.child and t.lev = @lev-1
left join @t x on x.parent = h.MgrID and x.child = h.EmpID
where x.parent is null
end

print 'helper table'
select * from @t order by fullpath
[/code]

sachingovekar
Posting Yak Master

101 Posts

Posted - 2010-01-12 : 09:06:32

Thanks a lot

Can you please help me in displaying the managername and employee names in the code above.

Regards,
Sachin

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-12 : 10:51:20

quote:
Originally posted by sachingovekar

Thanks a lot

Can you please help me in displaying the managername and employee names in the code above.

Regards,
Sachin

its a matter of small tweak


declare @MgrID
set @MgrID=2 --sample value
declare @t table (parent varchar(20), child varchar(20),childname varchar(100), lev int, fullpath varchar(8000))
declare @lev int
set @lev = 0

--Get Root node(s)
insert @t (parent, child,childname, lev, fullpath)
select distinct null, p.EmpID,p.EmpName, @lev, p.EmpName
from   dbo.emp p
where EmpID=@MgrID

while @@rowcount > 0
begin
       set @lev = @lev + 1

       --Get all children of current level
       insert @t (parent, child,childname, lev, fullpath)
       select h.MgrID, h.EmpID,h.EmpName, @lev, t.fullpath + '.' + h.EmpName
       from   @t t
       join   emp h on h.MgrID = t.child and t.lev = @lev-1
       left join @t x on x.parent = h.MgrID and x.child = h.EmpID
       where x.parent is null
end

print 'helper table'
select * from @t order by fullpath

sachingovekar
Posting Yak Master

101 Posts

Posted - 2010-01-13 : 00:56:00

Thanks will try this out.

Regards,
Sachin

sachingovekar
Posting Yak Master

101 Posts

Posted - 2010-01-13 : 05:14:35

I am not able to add manager name column.

can you please try.

Thanks for your efforts.

Regards,
Sachin

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-13 : 05:48:52

quote:
Originally posted by sachingovekar

I am not able to add manager name column.

can you please try.

Thanks for your efforts.

Regards,
Sachin

I have given you enough details. can you please try yourself tweaking given suggestion?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources