Create FUNCTION f_Cid(@ID char(10))
RETURNS @t_Level TABLE(PartID varchar(20),FatherPartID varchar(20),Level int)
AS
BEGIN
 DECLARE @Level int
 DECLARE @FatherPartID varchar(20)
 SET @Level=1
 set @FatherPartID=null
 INSERT @t_Level SELECT @ID,@FatherPartID,@Level
 WHILE @@ROWCOUNT>0
 BEGIN
  SET @Level=@Level+1
  INSERT @t_Level SELECT a.PartID,a.FatherPartID,@Level
  FROM Part a,@t_Level b
  WHERE a.FatherPartID=b.PartID
   AND b.Level=@Level-1
 END
 RETURN
END
GO
select * from f_Cid('GENEZYS')