TSQL recursive

recursive.sql

DECLARE @id INT
SET @id = 7

DECLARE @tmp TABLE (id INT , ParentId INT)
INSERT INTO @tmp VALUES(1,0)
INSERT INTO @tmp VALUES(2,0)
INSERT INTO @tmp VALUES(3,2);
INSERT INTO @tmp VALUES(4,3);
INSERT INTO @tmp VALUES(5,0);
INSERT INTO @tmp VALUES(6,0);
INSERT INTO @tmp VALUES(7,4);
INSERT INTO @tmp VALUES(8,5);

WITH parent AS
(
SELECT id, parentId from @tmp WHERE id = @id
UNION ALL 
SELECT t.id, t.parentId FROM parent
INNER JOIN @tmp t ON t.id = parent.parentid
)

SELECT id FROM parent
WHERE id <> @id;