求解函数递归问题

Question

有一张表结构及数据如下:
cMenuId    cMenuName   cParentMenuId  bEndGrade
MS01       系统设置        NULL           0
MS0101     选项设置        MS01           0
MS010101   查询            MS0101         1
MS010102   编辑            MS0101         1
MS0102     岗位设置        MS01           0
MS010201   查询            MS0102         1
MS010202   编辑            MS0102         1要求:将某一个菜单(如:MS01)下的所有bEndGrade的值为1的子菜单的cMenuId值计算到一个字符串中
我的方法:定义一个函数来解决,函数代码:
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = object_id(N'[dbo].[MS_SubMenuId]') AND xtype in (N'FN', N'IF', N'TF')) 
DROP FUNCTION MS_SubMenuId
GO
CREATE FUNCTION MS_SubMenuId(@cMenuId AS varchar(10))
RETURNS VarChar(500)
AS
BEGIN
    DECLARE @myMenuId AS varchar(10)
    DECLARE @myMenuName AS varchar(50)
    DECLARE @myEndGrade AS bit
    DECLARE @ReturnValue AS varchar(500)    SET @ReturnValue=''    DECLARE curRead CURSOR FOR 
       SELECT cMenuId,cMenuName,bEndGrade FROM MS_sysMenu WHERE cParentMenuId=@cMenuId
    OPEN curRead
    FETCH NEXT FROM curRead INTO @myMenuId,@myMenuName,@myEndGrade
    WHILE (@@FETCH_STATUS=0)
       BEGIN 
          IF @myEndGrade=1 
            IF @ReturnValue='' SET @ReturnValue =RTRIM(@myMenuId ) ELSE SET  @ReturnValue=@ReturnValue+'|'+RTrim(@myMenuId) 
          ELSE 
            IF @ReturnValue='' SET  @ReturnValue=dbo.MS_SubMenuId(@cMenuId ) ELSE SET @ReturnValue=@ReturnValue+'|'+dbo.MS_SubMenuId(@cMenuId)
          FETCH NEXT FROM curRead INTO @myMenuId,@myMenuName,@myEndGrade
       END
    CLOSE curRead
    DEALLOCATE curRead
    RETURN @ReturnValue
END
GO问题:
在查询分析器中运行:
PRINT dbo.MS_subMenuId('MS01')时出现下列错误:
服务器: 消息 217，级别 16，状态 1，过程 MS_SubMenuId，行 21
超出了存储过程、函数、触发器或视图的最大嵌套层数（最大层数为 32）。
请高手解答一下这个问题,谢谢!
可以提示其他解决要求思路的也可以:)

Answer 1

肯定是嵌套的层数太多了，sql中最多嵌套32层。

Answer 2

楼主可以参考一下，
http://topic.csdn.net/u/20080822/10/34e9c55f-33c0-43ee-a81f-c5fc8d7a11ce.html对树形数据的存储

Answer 3

--死循环了
CREATE FUNCTION MS_SubMenuId(@cMenuId AS varchar(10))
RETURNS VarChar(500)
AS
BEGIN
    DECLARE @myMenuId AS varchar(10)
    DECLARE @myMenuName AS varchar(50)
    DECLARE @myEndGrade AS bit
    DECLARE @ReturnValue AS varchar(500)    SET @ReturnValue=''    DECLARE curRead CURSOR FOR
      SELECT cMenuId,cMenuName,bEndGrade FROM MS_sysMenu WHERE cParentMenuId=@cMenuId
    OPEN curRead
    FETCH NEXT FROM curRead INTO @myMenuId,@myMenuName,@myEndGrade
    WHILE (@@FETCH_STATUS=0)
      BEGIN
          IF @myEndGrade=1
    BEGIN
            IF @ReturnValue='' SET @ReturnValue =RTRIM(@myMenuId ) ELSE SET  @ReturnValue=@ReturnValue+'|'+RTrim(@myMenuId)
RETURN @ReturnValue
END
          ELSE
            IF @ReturnValue='' SET  @ReturnValue=dbo.MS_SubMenuId(@cMenuId ) ELSE SET @ReturnValue=@ReturnValue+'|'+dbo.MS_SubMenuId(@cMenuId)
          FETCH NEXT FROM curRead INTO @myMenuId,@myMenuName,@myEndGrade
      END
    CLOSE curRead
    DEALLOCATE curRead
    RETURN @ReturnValue

Answer 4

是不是这个意思
还是我理解错了
DECLARE @TB TABLE(cMenuId VARCHAR(20),cMenuName VARCHAR(20),cParentMenuId VARCHAR(20),bEndGrade INT)
INSERT INTO @TB SELECT 'MS01','系统设置','NULL',0 
UNION ALL SELECT 'MS0101','选项设置','MS01',0 
UNION ALL SELECT 'MS010101','查询','MS0101',1 
UNION ALL SELECT 'MS010102','编辑','MS0101',1 
UNION ALL SELECT 'MS0102','岗位设置','MS01',0 
UNION ALL SELECT 'MS010201','查询','MS0102',1 
UNION ALL SELECT 'MS010202','编辑','MS0102 ',1 DECLARE @STR VARCHAR(800)
SELECT @STR=ISNULL(@STR,'')+'|'+ cMenuId FROM @TB WHERE cMenuId LIKE '%MS01%' AND bEndGrade=1
SELECT cMenuId=STUFF(@STR,1,1,'')/*
cMenuId                                                                                             
--------------------------------------------------------------------------------------------------- 
MS010101|MS010102|MS010201|MS010202（所影响的行数为 1 行）*/

Answer 5

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = object_id(N'[dbo].[MS_SubMenuId]') AND xtype in (N'FN', N'IF', N'TF')) 
DROP FUNCTION MS_SubMenuId 
GO 
CREATE FUNCTION MS_SubMenuId(@cMenuId AS varchar(10)) 
RETURNS VarChar(500) 
AS 
BEGIN 
    declare @t table (cMenuId varchar(10),-- cMenuName varchar(50),
                 cParentMenuId  varchar(10),bEndGrade bit,lev int)
    declare @lev int
    set @lev=0
    insert @t select cMenuId ,--cMenuName,
                     cParentMenuId,bEndGrade,@lev from MS_sysMenu WHERE cParentMenuId=@cMenuId 
    while exists (select 1 from MS_sysMenu a,@t t
                 WHERE a.cParentMenuId=t.cMenuId and t.lev=@lev
       )
       insert @t select a.cMenuId ,--a.cMenuName,
                 a.cParentMenuId,a.bEndGrade,@lev+1 from MS_sysMenu a,@t t
                 WHERE a.cParentMenuId=t.cMenuId and t.lev=@lev
      
    DECLARE @ReturnValue AS varchar(500) 
    select @ReturnValue=isnull(@ReturnValue+'|','')+cMenuId from @t where bEndGrade=1
    RETURN @ReturnValue 
END 
GO 

Answer 6

原来的死循环了         ELSE 
            IF @ReturnValue='' SET  @ReturnValue=dbo.MS_SubMenuId(@cMenuId ) ELSE SET @ReturnValue=@ReturnValue+'|'+dbo.MS_SubMenuId(@cMenuId)   
应该改成         ELSE 
            IF @ReturnValue='' SET  @ReturnValue=dbo.MS_SubMenuId(@cMyMenuId ) ELSE SET @ReturnValue=@ReturnValue+'|'+dbo.MS_SubMenuId(@cMyMenuId) 
  

Answer 7

我的写错了，更正下：IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = object_id(N'[dbo].[MS_SubMenuId]') AND xtype in (N'FN', N'IF', N'TF')) 
DROP FUNCTION MS_SubMenuId 
GO 
CREATE FUNCTION MS_SubMenuId(@cMenuId AS varchar(10)) 
RETURNS VarChar(500) 
AS 
BEGIN 
    declare @t table (cMenuId varchar(10),-- cMenuName varchar(50),
                 cParentMenuId  varchar(10),bEndGrade bit,lev int)
    declare @lev int
    set @lev=0
    insert @t select cMenuId ,--cMenuName,
                     cParentMenuId,bEndGrade,@lev from MS_sysMenu WHERE cParentMenuId=@cMenuId 
    while exists (select 1 from MS_sysMenu a,@t t
                 WHERE a.cParentMenuId=t.cMenuId and t.lev=@lev
       )
    begin
       insert @t select a.cMenuId ,--a.cMenuName,
                 a.cParentMenuId,a.bEndGrade,@lev+1 from MS_sysMenu a,@t t
                 WHERE a.cParentMenuId=t.cMenuId and t.lev=@lev
       set @lev=@lev+1
    end  
    DECLARE @ReturnValue AS varchar(500) 
    select @ReturnValue=isnull(@ReturnValue+'|','')+cMenuId from @t where bEndGrade=1
    RETURN @ReturnValue 
END 
GO