--查询所有子模块的表值函数
CREATE FUNCTION [dbo].[FN_GetSubModule] (@id AS INT )
RETURNS @temp TABLE
    (
      [id] INT ,
      [parentid] INT ,
      [Code] NVARCHAR(50) ,
      [FullName] NVARCHAR(200) ,
      [Category] NVARCHAR(50),
      [ImageIndex] NVARCHAR(50),
      [Target] NVARCHAR(100),
      [IsPublic] INT,
      [SortCode] INT,
      [levels] INT --levels表示栏目层次的，是必须有的！
    )
AS 
    BEGIN        
        DECLARE @level AS INT    --声明一个变量，用于保存栏目信息层次索引
        SELECT  @level = 0
        INSERT  INTO @temp
                SELECT  Id,
                        ParentId ,
                        Code ,
                        fullName ,
                        Category,
                        ImageIndex,
                        Target,
                        IsPublic,
                        SortCode,
                        @level
                FROM    dbo.PiModule
                WHERE   Id = @id AND DeleteMark = 0
    --先根据id获取根据节点的数据，@level初始为0，表示根目录
        WHILE @@rowcount > 0 
            BEGIN
                SET @level = @level + 1      
                INSERT  INTO @temp
                        SELECT  a.id ,
                                a.ParentId ,
                                a.Code ,
                                a.FullName ,
                                a.Category,
                                a.ImageIndex,
                                a.Target,
                                a.IsPublic,
                                a.SortCode,
                                @level
                        FROM    PiModule a
                                INNER JOIN @temp t ON a.parentid = t.id
                                                      AND t.levels = @level - 1   
                                                      AND a.DeleteMark = 0          
            END 
        RETURN    
    END

GO