erp基础档案管理模块中实现多级分类档案id号自动编码技术(v1.0)
本存储过程实现了多级分类档案id号自动编码技术,本版本(v1.0)现在只实现每级3位的编码,
本版本的特点是:
n 可以根据不同的数据库表产生不同的编码,达到通用化
n 调用时通过指定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 @[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
用户创建基础档案时可以按以下类似表格式创建:
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',@[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
依次类推,在此不举(注意执行时三个语句一起执行)
(完)
