SQL递归查询子类型

if   exists  ( select   *   from  dbo.sysobjects  where  id  =   object_id (N ' [dbo].[F_GetChildCate] ' )  and  xtype  in  (N ' FN ' , N ' IF ' , N ' TF ' ))
drop   function   [ dbo ] . [ F_GetChildCate ]
GO

SET  QUOTED_IDENTIFIER  OFF  
GO
SET  ANSI_NULLS  OFF  
GO

Create   Function  F_GetChildCate( @Id   int ) 
Returns    @Child   Table ( [ CateId ]   int ,CateName  nvarchar ( 127 ),ParentId  int )
As  
Begin  

     Begin             
          Insert   @Child   Select  Id,C_CateName,C_ParentID   From   [ T_Cate ]   Where   [ C_ParentID ]   =   @Id
         While   @@ROWCOUNT   >   0
                 Begin                      
             Insert   @Child   Select  B.Id,B.C_CateName,B.C_ParentID   From   @Child  A 
             Inner   Join   [ T_Cate ]  B  On  A. [ CateId ] = B. [ C_ParentID ]  
             Where  B. [ C_ParentID ]   Not   In  ( Select   Distinct   [ ParentId ]   From   @Child ) 
                 End  
         End
   
     Return  
End

GO
SET  QUOTED_IDENTIFIER  OFF  
GO
SET  ANSI_NULLS  ON  
GO

转载于:https://www.cnblogs.com/skyblue/archive/2008/07/23/1249621.html