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')
转载于:https://www.cnblogs.com/zqstc/archive/2010/08/12/1798508.html