Recursive Programming (Hierarchy Levels) - SQL SErver 2000 / 2005 !!

Hi,

I am looking more closely into CTE (Common Table Expressions) in SQL Server 2005. Can you please provide me sample code how we can achieve the below task WITHOUT using CTE.

-- Create a table

CREATE TABLE Employees
(
       EmpId      int,
       Ename      varchar(50),
       ReportsTo      int
)
GO

-- Insert some data

INSERT INTO Employees VALUES (1, 'Kishan', 2)
GO

INSERT INTO Employees VALUES (2, 'Horwitz', NULL)
GO

INSERT INTO Employees VALUES (3, 'James', 2)
GO

INSERT INTO Employees VALUES (4, 'Anurag', 2)
GO

INSERT INTO Employees VALUES (5, 'Gary', 2)
GO

INSERT INTO Employees VALUES (6, 'Bhalaram', 5)
GO

INSERT INTO Employees VALUES (7, 'Manish', 5)
GO

INSERT INTO Employees VALUES (8, 'Jia', 2)
GO

INSERT INTO Employees VALUES (9, 'sitaiah', 5)
GO

-- I need the following output

EmpID    EmpName         ReportsTo    HierarchyLevel
2             Horwitz             NULL                    0
4             Anurag                 2                        1
5             Gary                     2                        1
3             James                   2                        1
8             Jia                         2                        1
1             Kishan                  2                        1
6             Bhalaram              5                        2
7             Manish                  5                        2
9             Sitaiah                  5                        2

Appreciate your time and patience !!

Thanks  

kishore_peddiAsked:
Who is Participating?
 
YveauCommented:
This will do ...
It is set based per level, so MUCH faster that employee based !

-- create temp table
CREATE TABLE #E
(
       EmpId      int,
       Ename      varchar(50),
       ReportsTo      int,
       HierarchyLevel int
)
GO

declare @level int
select @level = 0

-- insert top level
insert
into    #e
select  *
,       @level
from    employees
where   reportsto is null

-- insert other levels by means of a loop
while (select count(*) from employees e1 left outer join #e e2 on e1.empid = e2.empid inner join #e e3 on e1.reportsto = e3.empid where e2.empid is null) > 0
begin
        select  @level = @level + 1

        insert
        into    #e
        select  e1.*
        ,       @level
        from    employees e1 left outer join #e e2 on e1.empid = e2.empid inner join #e e3 on e1.reportsto = e3.empid where e2.empid is null
end

-- show result:
select * from #e

Hope this helps ...
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Create FUNCTION dbo.RetEmpLevel (@EmpId INT ) RETURNS INT
 AS
 BEGIN
       DECLARE @Level INT, @ReportsTo int
       SELECT @ReportsTo = ReportsTo,@Level = 0
       FROM Employees
       WHERE EmpID = @EmpID
       
       WHILE @ReportsTo IS NOT NULL
       BEGIN
             SELECT @ReportsTo = ReportsTo,@Level = @Level+1
             FROM Employees
             WHERE EmpID = @ReportsTo
       
       END
 
       RETURN @Level
 
 END
 
 GO
 
 SELECT *, dbo.RetEmpLevel (EmpId) AS EmpLevel
 FROM Employees
0
 
Ted BouskillSenior Software DeveloperCommented:
Hmm, that function will be SLOW on a large dataset.  Our company has 8,000+ employees and one of my staff wrote a function like that and it was brutally slow.  Why don't you want to use the recursive functionality built into SQL 2005?
0
 
YveauCommented:
Glad I could be of any help and thanks for the grade !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.