September 24, 2002 at 7:35 pm
Hello,
In a very typical scenario I was looking to create a SQL that traverses a hierarchical structure. Simplified example below is supposed to return all supervisors of a given employee up the organization hierarchy. What is interesting is that no records are ever returned from the recursive call. Can anyone give some explanation on why would this occur.
create table employeelist(id int, parentid int, position varchar(20))
insert into employeelist values (1,null,'CEO')
insert into employeelist values (11,1,'CFO')
insert into employeelist values (12,1,'COO')
insert into employeelist values (13,1,'CTO')
insert into employeelist values (101,11,'Accounts Manager1')
insert into employeelist values (102,12,'Operations Manager1')
insert into employeelist values (103,13,' IT Manager1 ')
insert into employeelist values (101,11,'Accounts Manager2')
insert into employeelist values (102,12,'Operations Manager2')
insert into employeelist values (103,13,' IT Manager2 ')
insert into employeelist values (1001,101,'Accounts Rep1')
insert into employeelist values (1002,101,'Accounts Rep2')
insert into employeelist values (1003,101,'Accounts Rep3')
insert into employeelist values (1101,102,'Trader1')
insert into employeelist values (1102,102,'Trader2')
insert into employeelist values (1103,102,'Trader3')
insert into employeelist values (1104,102,'Trader4')
insert into employeelist values (1105,102,'Trader5')
insert into employeelist values (1106,102,'Trader6')
insert into employeelist values (1303,103,' DBA')
alter FUNCTION GetSupervisor
(@employeeid int)
RETURNS @groups TABLE (id int, parentid int, position varchar(20))
AS
begin
declare @parentid int
-- Get the employee supervisor ID
select @parentid = parentid from employeelist where id = @employeeid
-- Check if this is the end of the chain.
if (not (@parentid is null))
begin
-- Get the employee supervisor record
insert into @groups --union
select * from GetSupervisor(@parentid) where id = @employeeid and not (parentid is null)
end
-- Get the employee record
insert into @groups
select * from employeelist where id = @employeeid
return
end
GO
-- To execute:
select * from GetSupervisor(1003)
Sincerely,
Max
September 25, 2002 at 4:27 am
See if this helps
CREATE FUNCTION GetSupervisor
(@id int)
RETURNS @groups TABLE (id int, parentid int, position varchar(20))
AS
begin
--declare @parentid int
-- Get the employee supervisor ID
select @id = parentid from employeelist where id = @id
-- Check if this is the end of the chain.
WHILE 1 = 1
BEGIN
-- Get the employee supervisor record
INSERT INTO @groups --union
SELECT * FROM employeelist WHERE [id] = @id
select @id = parentid from employeelist where [id] = @id
IF @id IS NULL or EXISTS(SELECT * FROM @groups WHERE [id] = @id) -- Make sure not on a permanent loop and the id is not null
BEGIN
break
END
END
RETURN
END
GO
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
September 25, 2002 at 7:58 am
You need to remove the WHERE clause the "Get the employee supervisor record" select statement. This statement is causing you to never return any supervisor records because supervisor records will never have an id = @employeeid. Nice recursive example. Do you mind if I use it and put it on my website?
Here the change I think might work for you:
-- create table employeelist(id int, parentid int, position varchar(20))
--
--
-- insert into employeelist values (1,null,'CEO')
-- insert into employeelist values (11,1,'CFO')
-- insert into employeelist values (12,1,'COO')
-- insert into employeelist values (13,1,'CTO')
-- insert into employeelist values (101,11,'Accounts Manager1')
-- insert into employeelist values (102,12,'Operations Manager1')
-- insert into employeelist values (103,13,' IT Manager1 ')
-- insert into employeelist values (101,11,'Accounts Manager2')
-- insert into employeelist values (102,12,'Operations Manager2')
-- insert into employeelist values (103,13,' IT Manager2 ')
--
--
-- insert into employeelist values (1001,101,'Accounts Rep1')
-- insert into employeelist values (1002,101,'Accounts Rep2')
-- insert into employeelist values (1003,101,'Accounts Rep3')
--
-- insert into employeelist values (1101,102,'Trader1')
-- insert into employeelist values (1102,102,'Trader2')
-- insert into employeelist values (1103,102,'Trader3')
-- insert into employeelist values (1104,102,'Trader4')
-- insert into employeelist values (1105,102,'Trader5')
-- insert into employeelist values (1106,102,'Trader6')
--
-- insert into employeelist values (1303,103,' DBA')
--
alter FUNCTION GetSupervisor
(@employeeid int)
RETURNS @groups TABLE (id int, parentid int, position varchar(20))
AS
begin
declare @parentid int
-- Get the employee supervisor ID
select @parentid = parentid from employeelist where id = @employeeid
-- Check if this is the end of the chain.
if (not (@parentid is null))
begin
-- Get the employee supervisor record
insert into @groups --union
select * from GetSupervisor(@parentid) --where id = @employeeid and not (parentid is null)
end
-- Get the employee record
insert into @groups
select * from employeelist where id = @employeeid
return
end
GO
select * from GetSupervisor(1003)
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
October 2, 2002 at 3:11 pm
Thank you very much for the correction, this was definately an oversight on my part. You can use this example on the your site or in your work.
Sincerely
MZ
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply