[MSSQL]超简便的递归查询,获取父节点,通用于sql 2000

Create Table [dbo].[TypeList]
(
    ID int ,
    Title varchar(250),
    FID int
)
Insert Into [dbo].[TypeList] values(1,'衣服',0)
Insert Into [dbo].[TypeList] values(2,'上衣',1)
Insert Into [dbo].[TypeList] values(3,'外套',2)
Insert Into [dbo].[TypeList] values(4,'运动装',3)
Insert Into [dbo].[TypeList] values(5,'衬衫',2)
GO
-- =============================================
-- Author:        竹子 By http://www.fute53.com/
-- Create date: 2010-01-19
-- Description:    递归查询
-- =============================================
CREATE FUNCTION [dbo].[GetFatherPath]
(    
    @StartID int, 
    @Maxtimes int =10 
)
RETURNS @treeTable TABLE 
( 
    ID int, 
    Title varchar(250),
    FID int, 
    times int 
) 
   
Begin 
    IF @Maxtimes < 1 Set @Maxtimes =1 
    While(@Maxtimes > 0 And @StartID > 0)
    Begin
        Insert Into @treeTable select *,@Maxtimes from [TypeList] Where ID = @StartID
        select @Maxtimes = @Maxtimes - 1,@StartID = FID from [TypeList] Where ID = @StartID
        IF @StartID is Null  Set @StartID = 0
    End
return
End
GO
select * from [dbo].[GetFatherPath](4,10)
select * from [dbo].[GetFatherPath](4,1)
select * from [dbo].[GetFatherPath](5,10)
GO
drop table [dbo].[TypeList]
drop function [dbo].[GetFatherPath]