erp基礎(chǔ)檔案管理模塊中實(shí)現(xiàn)多級分類檔案id號自動(dòng)編碼技術(shù)(v1.0)
本存儲過程實(shí)現(xiàn)了多級分類檔案id號自動(dòng)編碼技術(shù),本版本(v1.0)現(xiàn)在只實(shí)現(xiàn)每級3位的編碼,
本版本的特點(diǎn)是:
n 可以根據(jù)不同的數(shù)據(jù)庫表產(chǎn)生不同的編碼,達(dá)到通用化
n 調(diào)用時(shí)通過指定iissubnode要產(chǎn)生的節(jié)點(diǎn)編碼是否是子結(jié)點(diǎn)還是兄弟節(jié)點(diǎn)來生成對應(yīng)編碼
進(jìn)行調(diào)用本存儲過程時(shí)需要注意的是需要傳遞節(jié)點(diǎn)的層次(或是叫節(jié)點(diǎn)的深度)
另外下一個(gè)版本(v2.0)將根據(jù)用戶自定義每級長度來實(shí)現(xiàn)更靈活的自動(dòng)編碼技術(shù)。
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 @[email protected]+1
set @[email protected]*3
set @sqlstring=n'select vid from '[email protected] +' 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 '[email protected] +' where vparentid in (select vid from #t) and vid not in (select vid from #t) and [email protected]'
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 '[email protected] +' where vid = '''+ltrim(rtrim(@vsourceid))+''''
exec(@sqlstring)
if @@rowcount > 0
begin
set @sqlstring=n'select @vtempid =isnull(max(vid),''0'') from '[email protected]+' where vid in (select vid from '[email protected]+' where [email protected])'
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
用戶創(chuàng)建基礎(chǔ)檔案時(shí)可以按以下類似表格式創(chuàng)建:
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查詢分析器進(jìn)行測試時(shí)可用如下方法進(jìn)行測試:
declare @value varchar(30)
exec prcidautogen '',0,1,'customerclass',@[email protected] output
select @value
insert customerclass values('001','a','a',null,1)
declare @value varchar(30)
exec prcidautogen '001',1,1,'customerclass',@[email protected] output
select @value
insert customerclass values('001001','b','b','001',2)
declare @value varchar(30)
exec prcidautogen '001',1,1,'customerclass',@[email protected] output
select @value
declare @value varchar(30)
exec prcidautogen '001001',2,0,'customerclass',@[email protected] output
select @value
依次類推,在此不舉(注意執(zhí)行時(shí)三個(gè)語句一起執(zhí)行)
(完)
