SqlServer:获取指定分类Id及其下属的所有子分类Id

分类表的表结构如下:

CREATE TABLE [dbo].[tbCategory](
    [Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [ParentId] [int] NOT NULL,
    [CateName] [nvarchar](50) NOT NULL,
    [DelFlag] [bit] NOT NULL
    )

函数如下:

-- =============================================
-- Author:        iKwok
-- Create date: 2012年4月24日
-- Description:    获取指定分类Id及其下属的所有子分类Id
-- =============================================
CREATE FUNCTION [dbo].[f_GetCategoryList]
(    
     @Id int
)
RETURNS TABLE 
AS
RETURN 
(
    with T as
        (
            select Id,ParentId,CateName,DelFlag from tbCategory where Id=@Id and DelFlag=0
            union all
            select tbSub.Id,tbSub.ParentId,tbSub.CateName,tbSub.DelFlag 
            from tbCategory as tbSub join T as tbTotal on tbSub.ParentId=tbTotal.Id 
            where tbSub.DelFlag=0 and tbTotal.DelFlag=0
        )
        
        select Id,ParentId,CateName from T
)
GO