Biegral Blog

GO
/****** Object:  UserDefinedFunction [dbo].[Func_select_goods_class_child_id]    Script Date: 02/05/2015 09:03:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[Func_select_goods_class_child_id](@ID int,@isSelf INT)  
returns @t table(ID int,ParentID int,layout int)  
as  
begin  
    declare @i int  
    set @i = 1  
    if(@isSelf=1)
    begin
      insert into @t select @ID,@ID,0 --当前级，本级，如果不要的话可以注释掉或再加个参数来选择操作  
    end
	insert into @t select ID,ParentID,@i from dbo.tb_goods_class where parentID = @ID    
    while @@rowcount<>0  
    begin  
        set @i = @i + 1  
        insert into @t  
        select  
            a.ID,a.ParentID,@i  
        from  
            dbo.tb_goods_class a,@t b  
        where  
            a.ParentID=b.ID  and b.layout = @i-1  
    end  
    return  
end

调用方法 ：

select * from Func_select_goods_class_child_id(类别id,是否包括本身(0,1))