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 @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
依次类推,在此不举(注意执行时三个语句一起执行)
(完)
文章来源于领测软件测试网 https://www.ltesting.net/
