SQL Server 2000 中递归

CREATE FUNCTION dbo.Fun_Get_Category_TreeInfo ( @CategoryID varchar(10) -- 若 @CategoryID = "" 则表示查询所有的节目,也就是父级代码为 -1 下的所有节目 ) RETURNS @TreeInfo Table ( [Category_id] [int] not null, [Parent_id] [int] null, [Category_name] [varchar](50) null, [Code] [varchar](10) null, [AllowDownAll] [char](1), [Operator_id] [smallint], [Define_time] [datetime], [Flag] [char](1), [categorypath] [varchar](100), [Level] [int] NOT NULL, [LevelFlag] [varchar] (200) NOT NULL ) AS BEGIN DECLARE @level As int SELECT @level = 0 if @CategoryID != '' Insert Into @TreeInfo SELECT Category_ID, Parent_ID, Category_Name, Code, allowDownAll,Operator_ID,Define_Time,Flag,Categorypath,@level, 'NULL-> ' + Cast(Category_ID As varchar(10)) FROM [Act_Category] WHERE [Category_ID] = @CategoryID else Insert Into @TreeInfo SELECT Category_ID, Parent_ID, Category_Name, Code, allowDownAll,Operator_ID,Define_Time,Flag,Categorypath,@level, 'NULL-> ' + Cast(Category_ID As varchar(10)) FROM [Act_Category] WHERE [Parent_ID] = -1 WHILE @@ROWCOUNT > 0 BEGIN SET @level = @level + 1 INSERT INTO @TreeInfo SELECT E.[Category_ID], E.[Parent_ID], E.[Category_Name], E.[Code], E.[allowDownAll],E.[Operator_ID],E.[Define_Time],E.[Flag],E.[Categorypath], @level, T.[LevelFlag] + '->' + Cast(E.[Category_ID] As varchar(10)) FROM [Act_Category] AS E JOIN @TreeInfo AS T ON E.[Parent_ID] = T.[Category_ID] AND T.[Level] = @level - 1 END Return END