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
