ERP基礎檔案管理模組中實現多級分類檔案ID號自動編碼技術(V1.0)

            ERP基礎檔案管理模組中實現多級分類檔案ID號自動編碼技術(V1.0)

 

    本預存程序實現了多級分類檔案ID號自動編碼技術,本版本(V1.0)現在只實現每級3位的編碼,

本版本的特點是:
  
    可以根據不同的資料庫表產生不同的編碼,達到通用化

    調用時通過指定iIsSubNode要產生的節點編碼是否是子結點還是兄弟節點來產生對應編碼

進行調用本預存程序時需要注意的是需要傳遞節點的層次(或是叫節點的深度)

另外下一個版本(V2.0)將根據使用者自訂每級長度來實現更靈活的自動編碼技術。

 

CREATE procedure prcIDAutoGen

@vSourceID varchar(30),

@iDepth int,

@iIsSubNode int,

@Table varchar(20),

@vIncrement varchar(30) output

as

begin

declare @iLen int

declare @vTempID varchar(30)

declare @SQLString nvarchar(500)

if @iIsSubNode =1

begin

 set @iDepth=@iDepth+1

 set @iLen=@iDepth*3

 set @SQLString=N"select vID from "+@Table +" where vID = """+ltrim(rtrim(@vSourceID))+""""

 exec(@SQLString)

 if @@rowcount > 0

 begin

  select @vSourceID as vID into #t

  set @SQLString=N"insert #t select vID from "+@Table +" where vParentID in (select vID from #t)  and vID not in (select vID from #t) and iDepth=@iDepth"

  exec sp_executesql @SQLString,N"@iDepth int",@iDepth

  if @@rowcount > 0

  begin

   set @SQLString=N"select @vTempID =isnull(max(vID),""0"") from #t"

   exec sp_executesql @SQLString,N"@vTempID varchar(30) output",@vTempID  output      

   set @SQLString="select @vIncrement=right(""000""+cast((cast(substring(@vTempID,1,@iLen) as
decimal(30,0))+1)as varchar),@iLen)"

   exec sp_executesql @SQLString,N"@vIncrement varchar(30) output,@vTempID varchar(30),@iLen int",@vIncrement out,@vTempID,@iLen

  end

  else

  begin

   select          @vIncrement=ltrim(rtrim(@vSourceID))+"001"

  end

 end

 else

 begin

  select @vIncrement="001"

 end

end

else

begin

 set @iLen=len(ltrim(rtrim(@vSourceID)))

 set @SQLString=N"select vID from "+@Table +" where vID = """+ltrim(rtrim(@vSourceID))+""""

 exec(@SQLString)

 if @@rowcount > 0

 begin

   set @SQLString=N"select  @vTempID =isnull(max(vID),""0"") from "+@Table+" where vID in (select vID from "+@Table+" where iDepth=@iDepth)"

   exec sp_executesql @SQLString,N"@vTempID varchar(30) output,@iDepth int",@vTempID  output,@iDepth    

   set @SQLString="select @vIncrement=right(""000""+cast((cast(substring(@vTempID,1,@iLen) as decimal(30,0))+1)as varchar),@iLen)"

   exec sp_executesql @SQLString,N"@vIncrement varchar(30) output,@vTempID varchar(30),@iLen int",@vIncrement out,@vTempID,@iLen

 end

 else

 begin

   select @vIncrement="001"

 end

end

end

 

使用者建立基礎檔案時可以按以下類似表格式建立:

create table CustomerClass(

vID    varchar(30) constraint pkCustomerClass primary key ,

vCustomerClassName varchar(40) NOT NULL,

vRemarks         varchar(80) NULL,

vParentID varchar(30) NULL,

iDepth       Int NOT NULL

)

 

另外使用者如果要在SQL查詢分析器進行測試時可用如下方法進行測試:

declare @value varchar(30)

exec prcIDAutoGen "",0,1,"CustomerClass",@vIncrement=@value output

select @value

 

insert customerclass values("001","a","a",null,1)

 

declare @value varchar(30)

exec prcIDAutoGen "001",1,1,"CustomerClass",@vIncrement=@value output

select @value

 

insert customerclass values("001001","b","b","001",2)

 

declare @value varchar(30)

exec prcIDAutoGen "001",1,1,"CustomerClass",@vIncrement=@value output

select @value

 

declare @value varchar(30)

exec prcIDAutoGen "001001",2,0,"CustomerClass",@vIncrement=@value output

select @value

 

依次類推,在此不舉(注意執行時三個語句一起執行)