sql 2种递归

alter FUNCTION f_Cid(@ID int)
RETURNS @t_Level TABLE(ID int,Level int)
AS
BEGIN
 DECLARE @Level int
 SET @Level=1
 INSERT @t_Level SELECT @ID,@Level
 WHILE @@ROWCOUNT>0
 BEGIN
  SET @Level=@Level+1
  INSERT @t_Level SELECT a.[MenuId],@Level
  FROM [MMS_Menu] a,@t_Level b
  WHERE a.[MenuParentId]=b.ID
   AND b.Level=@Level-1
 END
 RETURN
END
GO
select * from f_Cid(101) b,[MMS_Menu] a where a.MenuId=b.ID

或  with cte as
(
select a.ID,a.date,a.name,a.pID from dbo.testb a where a.ID=@pid
union all  
select k.ID,k.date,k.name,k.pID  from dbo.testb k inner join cte c on c.ID = k.pID
)
 select * from cte OPTION (MAXRECURSION 0); ;