SQL 递归生成树

捡到一个题目，题目如下，不清楚自己做的对于不对，还请各位看官指正。

题目：

构建数据表smMenu，主要字段有：ID、菜单名、菜单类名、上级菜单ID

测试用初始数据如下：

销售管理

         报价管理

                   制订报价单

                   报价单查询

         订单管理

                   订单录入

                   订单审核

计划管理

         主计划

                   MRP计划

用递归算法将菜单数据插入到原生Tree控件，显示所有菜单（按上下级结构显示）

要求写出递归函数的定义及简单调用代码。

USE E8RFIDEng
go

/*************************************************************
** creator:JustinYang DateTime 2016-03-26
** function:针对表生成树
** 传入参数： 无
** 输出参数：@smMenu
****************************************************************/
create procedure sp_MakeTeee
as 
begin
	UPDATE smMenu SET upperid = id 	WHERE upperid IS NULL 	OR upperid = 0
    DECLARE @smMenu Table(id INT,treeid VARCHAR(6000),menuNM VARCHAR(50),upperMenu VARCHAR(50),upperid INT)
	--遍历所有根节点,其中调用递归函数生成树
	DECLARE maketree CURSOR FAST_FORWARD FOR --建立游标
	SELECT id FROM smMenu WITH (NOLOCK)	WHERE ISNULL(treeid, 0) = 0 AND upperid = id ORDER BY upperid ASC, id ASC
	OPEN maketree

	DECLARE @id INT
	DECLARE @childorderid INT
	SET @childorderid = 0

	DECLARE @treeid VARCHAR(100)
	DECLARE @sId VARCHAR(2000) --已经处理过的节点
	SET @sid = ','

	DECLARE @streeid VARCHAR(2000) --已经处理过的节点树节点，与@sid一一对应
	SET @streeid = ','

	FETCH NEXT FROM maketree INTO @id
	WHILE @@fetch_status = 0
	BEGIN
		IF CHARINDEX(',' + CAST(@id AS VARCHAR(10)) + ',',@sid) < 0 
		BEGIN
			SET @childorderid = @childorderid + 1
			SET @treeid = CAST(@childorderid AS VARCHAR(3))
			WHILE (LEN(@treeid) < 2) 
			BEGIN
			SET @treeid = '0' + @treeid
			END
			SET @treeid = '1' + @treeid

			--将当前节点加入已经处理节点
			SET @sId = @sID + CAST(@id AS VARCHAR(10)) + ','
			SET @streeid = @streeid + @treeid + ','
			
            --递归处理子节点
            INSERT INTO @smMenu(id,treeid,menuNM,upperMenu,upperid) 
            SELECT id,treeid,menuNM,upperMenu,upperid FROM dbo.diGuiMakeTree(@id, @treeid, @sId)
            
            --加入根节点
            INSERT INTO @smMenu
                    ( id ,
                      treeid ,
                      menuNM ,
                      upperMenu ,
                      upperid
                    )
            VALUES  ( @id , -- id - int
                      @treeid , -- treeid - varchar(6000)
                      '' , -- menuNM - varchar(50)
                      '' , -- upperMenu - varchar(50)
                      0  -- upperid - int
                    )
            
		END
		FETCH NEXT FROM maketree INTO @id
	END
	SELECT * FROM @smMenu--返回需要的结果，treeid即为树id
	CLOSE maketree
	DEALLOCATE maketree
END

GO
/*******************************************************
** creator:JustinYang DateTime:2016-03-26
** function:处理树的当前节点,获取
** 输入参数：@iid当前节点ID
**           @itreeid 当前节点树ID
**           @sId 已经处理过的节点  用于优化作用
** 输出参数：@smMenu表          
** 调用方式：Select dbo.diGuiMakeTree()
*************************************************************/
create function diGuiMakeTree(
   @iid int,--当前节点
   @itreeid INT,
   @sId varchar(2000) 
)
RETURNS @maketreetable TABLE(id INT,treeid VARCHAR(6000),menuNM VARCHAR(50),upperMenu VARCHAR(50),upperid INT)
as
begin
	
    --DECLARE @maketreetable Table(id INT,treeid VARCHAR(6000),menuNM VARCHAR(50),upperMenu VARCHAR(50),upperid INT)
   
	declare maketree cursor FAST_FORWARD for --建立游标
	SELECT id FROM smMenu WITH (NOLOCK) WHERE ISNULL(treeid, 0) = 0 AND upperid = @iid AND CHARINDEX(','+CAST(@iid AS VARCHAR(10))+',',@sId)<0 ORDER BY upperid ASC, id ASC
	OPEN maketree
	
	DECLARE @childorderid INT
	SET @childorderid = 0--兄弟节点个数
	DECLARE @id INT --当前处理节点
	FETCH NEXT FROM maketree INTO @id
	WHILE @@fetch_status = 0
	BEGIN
		IF (CHARINDEX(',' + CAST(@id AS VARCHAR(10)) + ',',@sid) > 0)
		BEGIN
			DECLARE @treeid VARCHAR(100)--当前节点对应的树节点            
            --计算treeid
			SET @childorderid = @childorderid + 1
			SET @treeid = CAST(@childorderid AS VARCHAR(3))
			WHILE (LEN(@treeid) < 3)
			BEGIN
				SET @treeid = '0' + @treeid
			END
			SET @treeid = @itreeid + @treeid
			
			SET @sId = @sId + CAST(@id AS VARCHAR(10)) + ','
			
            --递归处理子节点
            INSERT INTO @maketreetable(id,treeid,menuNM,upperMenu,upperid) 
            SELECT id,treeid,menuNM,upperMenu,upperid FROM dbo.diGuiMakeTree(@id, @treeid, @sId)
            
            --处理当前节点
            INSERT INTO @maketreetable
                    ( id ,
                      treeid ,
                      menuNM ,
                      upperMenu ,
                      upperid
                    )
            VALUES  ( @id , -- id - int
                      @treeid , -- treeid - varchar(6000)
                      '' , -- menuNM - varchar(50)
                      '' , -- upperMenu - varchar(50)
                      @iid  -- upperid - int
                    )
            
		END
		--跳转到下一个兄弟节点
		FETCH NEXT FROM maketree INTO @id     
	END
	CLOSE maketree
	DEALLOCATE maketree
	RETURN
END

真诚欢迎指正！