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;