USE master
GO


DECLARE @InTemp TABLE(ID INT,PARENT_ID INT);
INSERT @InTemp(ID,PARENT_ID)
 VALUES(9,0),(999,9),(99999,999);

DECLARE @InStart INT;
SELECT @InStart=99999;

WITH CTE AS
(
  SELECT ID,PARENT_ID
  FROM @InTemp
  WHERE ID=@InStart
  
  UNION ALL
  
  SELECT TAB.ID,TAB.PARENT_ID
  FROM @InTemp AS TAB
  JOIN CTE AS X ON TAB.ID=X.PARENT_ID
  
  
) 
SELECT C.ID,C.PARENT_ID
FROM CTE AS C
OPTION (MAXRECURSION 100)