获取 当前类别所有子类别及 类别相应的深度

GO/****** Object:  UserDefinedFunction [dbo].[Func_select_goods_class_child_id]    Script Date: 02/05/2015 09:03:22 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER 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 --当前级，本级，如果不要的话可以注释掉或再加个参数来选择操作      endinsert 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))