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 ;