Ahhh, ye olde adjacency list model. You might get better efficiency from a set-based solution, like the nested sets model or one of its variants. Here's an Q&D sample.
CREATE TABLE #Employees (ID INT PRIMARY KEY,
LastName VARCHAR(30),
FirstName VARCHAR(30),
LeftID INT,
RightID INT)
INSERT INTO #Employees (ID, LastName, FirstName, LeftID, RightID)
VALUES (1, 'Smith', 'John', 1, 24)
INSERT INTO #Employees (ID, LastName, FirstName, LeftID, RightID)
VALUES (2, 'Johnson', 'Bob', 2, 15)
INSERT INTO #Employees (ID, LastName, FirstName, LeftID, RightID)
VALUES (4, 'Fields', 'Sue', 3, 4)
INSERT INTO #Employees (ID, LastName, FirstName, LeftID, RightID)
VALUES (3, 'Burton', 'Debra', 16, 23)
INSERT INTO #Employees (ID, LastName, FirstName, LeftID, RightID)
VALUES (5, 'Jacobs', 'Todd', 11, 14)
INSERT INTO #Employees (ID, LastName, FirstName, LeftID, RightID)
VALUES (6, 'Sanders', 'Mary', 5, 10)
INSERT INTO #Employees (ID, LastName, FirstName, LeftID, RightID)
VALUES (10, 'Sims', 'Tom', 17, 18)
INSERT INTO #Employees (ID, LastName, FirstName, LeftID, RightID)
VALUES (11, 'Wright', 'Larry', 19, 20)
INSERT INTO #Employees (ID, LastName, FirstName, LeftID, RightID)
VALUES (12, 'Morgan', 'Betty', 21, 22)
INSERT INTO #Employees (ID, LastName, FirstName, LeftID, RightID)
VALUES (7, 'Teeter', 'Sandy', 12, 13)
INSERT INTO #Employees (ID, LastName, FirstName, LeftID, RightID)
VALUES (8, 'Morris', 'Megan', 6, 7)
INSERT INTO #Employees (ID, LastName, FirstName, LeftID, RightID)
VALUES (9, 'Binks', 'Randy', 8, 9)
GO
CREATE PROCEDURE dbo.GetOrgChart (@startID INT)
AS
BEGIN
SELECT e1.ID, e1.LastName, e1.FirstName, e1.LeftID, e1.RightID, COUNT(e2.ID) AS Level
FROM #Employees e1
INNER JOIN #Employees e2
ON e1.LeftID BETWEEN e2.LeftID AND e2.RightID
WHERE e1.LeftID BETWEEN (SELECT LeftID FROM #Employees WHERE ID = @startid)
AND (SELECT RightID FROM #Employees WHERE ID = @startid)
GROUP BY e1.ID, e1.LastName, e1.FirstName, e1.LeftID, e1.RightID
ORDER BY e1.LeftID
END
GO
EXEC dbo.GetOrgChart 1