USE [DNF_09] GO /****** Object:  UserDefinedFunction [dbo].[Dnff_sys_GetTree]    Script Date: 11/07/2011 16:34:10 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER ON GO

-- Drop FUNCTION  Dnff_sys_GetTree ALTER FUNCTION  [dbo].[Dnff_sys_GetTree]( /*    delete Dnft_tra_TreeCode    insert Dnft_tra_TreeCode(key_seq,link_key_seq,Tree_Name,SortID) select key_seq,link_key_seq,menu_name,SortID from Dnft_sys_Menu    select * from dbo.Dnff_sys_GetTree('root',100,'   ','')    delete Dnft_tra_TreeCode */ @key_seq_P  varchar(Max), @iLevel  int, @Tree_NameFill_P  varchar(Max), @Tree_Code_P  varchar(Max) )RETURNS @re TABLE(Tree_Name nvarchar(Max),Tree_Code nvarchar(Max),key_seq varchar(50),SortID int)

AS   BEGIN  

declare @Tree_Name nvarchar (100)  declare @key_seq varchar (50)   declare @iLevelIndex  int;set @iLevelIndex=0 declare crRoll cursor for   select Tree_Name,key_seq from Dnft_tra_TreeCode (nolock) where link_key_seq=@key_seq_P order by SortID asc,up_dtime desc

open crRoll   while 1=1  --@i<@count     begin         fetch next from crRoll into  @Tree_Name,@key_seq       if @@fetch_status <> 0 break           begin                declare @Tree_Code varchar(Max);                set @Tree_Code=case when @Tree_Code_P=''                               then '@-'+dbo.Dnff_sql_IntToString(@iLevelIndex+1,4)                               else @Tree_Code_P+'-'+dbo.Dnff_sql_IntToString(@iLevelIndex+1,4)                               end                               -- Add Node                insert @re values(@Tree_NameFill_P+@Tree_Name,@Tree_Code ,@key_seq,-1)                               -- Call MySelf                if (select count(*) from Dnft_tra_TreeCode (nolock) where link_key_seq=@key_seq)>0 and @iLevelIndex<@iLevel                  begin                     declare @iLevelSub int;set @iLevelSub=@iLevel-1                     insert into @re                       select isnull(@Tree_NameFill_P+Tree_Name,''),Tree_Code ,key_seq,-1                       from dbo.Dnff_sys_GetTree(@key_seq,@iLevelSub,@Tree_NameFill_P,@Tree_Code)                  end               set @iLevelIndex=@iLevelIndex+1         end     end    close crRoll  deallocate crRoll

--更新SortID         update @re set SortID=convert(int,case when len(isnull(tree_Code,'-1'))<6 then '-1' else right(tree_Code,4) end)

RETURN   END