【原】使用SQL递归查询得到一个分类的路径

在分类表中有这样的几个字段cid：分类ID，name：分类名字，pid：父类ID。如下描述：

Categories（cid,name,pid）

现在要得到这样的一个结果：分类/分类/分类/分类/........

 

create   function     [ dbo ] . [ getPath ] ( @NodeId     int )   
returns     varchar ( 8000 )   
as    
begin    
           declare     @first     varchar ( 8000 ),
                     @second       varchar ( 8000 ),
                     @ParentId     int ,
                     @result       varchar ( 8000 )
           set     @first     =   ''   
           set      @second    =   ''  
           set      @result    =   ''
           select    @first = categoryName, @ParentId = CategoryRootID
           from     wdk.dbo.categories
           where    categoryID = @NodeId    
           while     @@rowcount <> 0    
           begin
                   set      @result =+ ' ' + @second + @result ;   
                   set     @NodeId = @ParentId  
                   select     @second = categoryName, @ParentId = categoryRootID
                   from    wdk.dbo.categories
                   where   categoryID = @NodeId ;    
                                
           end    
           -- set    @result=@result+@second+'';
           set     @result = @result + @first ;
           return     @result    
end