--创建递归函数
create FUNCTION getGroupIds(@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 menuList a,@t_Level b
WHERE a.menuParentID=b.ID
AND b.Level=@Level-1
END
RETURN
END
--调用
select * from getGroupIds(1)