Sql 2005/2008 recursive query

Let's have the following example:
We have a table with and Id and ParentId hold relations between employees and we want to see all employees that are under a manager or team leader.
First the sql table and data

CREATE TABLE [dbo].[TreeEmployees](
  [Id] [bigint] NOT NULL,
  [ParentId] [bigint] NULL,
  [Name] [varchar](50) NOT NULL,
  [Description] [varchar](200) NOT NULL,
  [Position] [varchar](50) NOT NULL,
CONSTRAINT [PK_Tree] PRIMARY KEY CLUSTERED
(
  [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[TreeEmployees] ADD  CONSTRAINT [DF_Tree_Name]  DEFAULT ('') FOR [Name]
GO
ALTER TABLE [dbo].[TreeEmployees] ADD  CONSTRAINT [DF_Tree_Description]  DEFAULT ('') FOR [Description]
GO
ALTER TABLE [dbo].[TreeEmployees] ADD  CONSTRAINT [DF_Tree_Position]  DEFAULT ('') FOR [Position]
GO


Add sql data

INSERT [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [Position]) VALUES (1, NULL, N'John Doe', N'John folowers', N'Technical Manager')
INSERT [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [Position]) VALUES (2, NULL, N'Jan Right', N'Jan folowers', N'Chief Account manager')
INSERT [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [Position]) VALUES (3, 1, N'Martin Flynn', N'Flyn team', N'Team Leader')
INSERT [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [Position]) VALUES (4, 1, N'Joyce May Ann', N'Joyce team', N'Team Leader')
INSERT [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [Position]) VALUES (5, 3, N'Mia Donovan', N'Meber of Fylnn''s team', N'Worker')
INSERT [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [Position]) VALUES (6, 3, N'Laurel Bean', N'Member of Flynn''s team', N'Worker')
INSERT [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [Position]) VALUES (7, 4, N'Anna Mendez', N'Member of Joyce''s team', N'Worker')
INSERT [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [Position]) VALUES (8, 4, N'Maria Fernandez', N'Member of Joyce''s team', N'Worker')
INSERT [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [Position]) VALUES (9, 2, N'Carter Bowls', N'Jan''s team 1', N'Team Leader')
INSERT [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [Position]) VALUES (10, 2, N'Jimmy Clarck', N'Jan''s team 2', N'Team Leader')
INSERT [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [Position]) VALUES (11, 9, N'Sully Westwood', N'Carter''s team', N'Worker')
INSERT [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [Position]) VALUES (12, 9, N'Louise Senna', N'Carter''s team', N'Worker')
INSERT [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [Position]) VALUES (13, 10, N'Lilly Ann Parker', N'Jimmy''s team', N'Worker')
INSERT [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [Position]) VALUES (14, 10, N'Tina Riggs', N'Jimmy''s team', N'Worker')

And here is the recursive query:

declare @rootId bigint
--set this to 1 or 2
set @rootId = 2;
WITH  TeamMembers  as
(
select Id, ParentId, Name, [Description],[Position], 0 as [level] from TreeEmployees
where Id=@rootId
union all
select tc.id, tc.ParentId, tc.Name, tc.[Description],tc.[Position],[level] +1 as [level] from TreeEmployees as tc
inner join TeamMembers on tc.ParentId = TeamMembers.Id
)
select * from TeamMembers

And here are the results:

Id	ParentId	Name	Description	Position	level
2	NULL	Jan Right	Jan folowers	Chief Account manager	0
9	2	Carter Bowls	Jan's team 1	Team Leader	1
10	2	Jimmy Clarck	Jan's team 2	Team Leader	1
13	10	Lilly Ann Parker	Jimmy's team	Worker	2
14	10	Tina Riggs	Jimmy's team	Worker	2
11	9	Sully Westwood	Carter's team	Worker	2
12	9	Louise Senna	Carter's team	Worker	2