SQL获取所有祖先菜单，类别

alter FUNCTION  [dbo].[fu_GetCategoryParentPowerID](@powerid int)RETURNS  nvarchar(500)asbegin-----------------------------declare @powerid int--set @powerid=7declare @tempTable TABLE    (        [ID] int,[Name] varchar(100),         [ParentCategoryId] int             )declare @nav nvarchar(500)declare @navName nvarchar(100)    declare @oldId int    declare @ID int    set @ID=@powerid    set @oldId=@IDset @nav=''set @navName=''    while(@ID>0)    begin        select @ID=ParentCategoryId,@navName=Name from Categories where ID=@ID;            if @oldId=@ID                    break            if(@ID>0)            begin             insert into @tempTable select ID,Name,ParentCategoryId from Categories where ID=@ID;            endset @nav=@nav+'>'+@navName    end    --select * from @tempTable --order by id asc--select @nav RETURN @navend---select dbo.fu_GetCategoryParentPowerID(7)--------------