kishore_peddi
asked on
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Glad I could be of any help and thanks for the grade !