递归获取子类

ALTER FUNCTION [dbo].[GetClassList](@ClassID INT)
  RETURNS @ResultTable TABLE(ID INT,Level INT)
  AS
BEGIN
  DECLARE @Level INT
  SET @Level = 1
  INSERT @ResultTable SELECT @ClassID,@Level
 
  WHILE @@ROWCOUNT>0
   BEGIN
    SET @Level = @Level + 1
    INSERT @ResultTable SELECT A.ID,@Level FROM ProductClass AS A,@ResultTable AS B
    WHERE A.ParentID = B.ID AND B.Level = @Level - 1
   END
  RETURN
END