34,587
社区成员
发帖
与我相关
我的任务
分享
if object_id('hzfa') is not null drop table hzfa
create table hzfa(id int identity,bh varchar(100))
insert hzfa(bh)
select '01'
union all select '0101'
union all select '01017710'
union all select '02'
union all select '0202'
union all select '02020020'
union all select '03'
union all select '0303'
union all select '03030030'
declare
@tablename sysname,
@fieldname sysname,
@CodeRule varchar(50),
@code varchar(50) ---要删除的节点编码
select @tablename='hzfa',@fieldname='bh',@CodeRule='1,2,3',@code='01'
--生成被删除节点的子节点的新编码规则(因为子节点要提升一级)
declare @codelen int,
@codelens int,
@CodeRule_new varchar(50)
select @codelens=len(@code),
@CodeRule_new=@CodeRule+','
--获取被删除的编码的最后一级的长度,并生成该编码之后编码规则
while @CodeRule_new>'' and @codelens>0
begin
select @codelen=LEFT(@CodeRule_new,charindex(',',@CodeRule_new)-1),
@CodeRule_new=STUFF(@CodeRule_new,1,charindex(',',@CodeRule_new),''),
@codelens=@codelens-@codelen
end
print @codelen 2
print @CodeRule_new 3,
print @codelens -1
--确认要删除的编码是否符合编码规则
if @codelens<>0
begin
raiserror(N'编码不符合指定的编码规则"%s"',16,1,@code,@CodeRule)
return
end
print @CodeRule_new
declare
@tablename sysname,
@fieldname sysname,
@CodeRule varchar(50),
@code varchar(50) ---要删除的节点编码
select @tablename='hzfa',@fieldname='bh',@CodeRule='2,3,4',@code='01001'
--生成被删除节点的子节点的新编码规则(因为子节点要提升一级)
declare @codelen int,
@codelens int,
@CodeRule_new varchar(50)
select @codelens=len(@code),
@CodeRule_new=@CodeRule+','
--获取被删除的编码的最后一级的长度,并生成该编码之后编码规则
while @CodeRule_new>'' and @codelens>0
begin
select @codelen=LEFT(@CodeRule_new,charindex(',',@CodeRule_new)-1),
@CodeRule_new=STUFF(@CodeRule_new,1,charindex(',',@CodeRule_new),''),
@codelens=@codelens-@codelen
end
print @codelen
print @CodeRule_new
print @codelens
--确认要删除的编码是否符合编码规则
if @codelens<>0
begin
raiserror(N'编码不符合指定的编码规则"%s"',16,1,@code,@CodeRule)
return
end
--生成修改被删除节点子节点编码的新编码规则
set @CodeRule_new=case when @CodeRule_new='' then @CodeRule_new
else STUFF(@CodeRule,2+len(@CodeRule)-charindex(',',reverse(@CodeRule)+',',len(@CodeRule_new)+2),0,'0,')
end
print @CodeRule_new
--得到被删除节点子节点编码更新和T-sql
declare @sql nvarchar(4000)
set @sql=dbo.f_changecoderule(@coderule,@CodeRule_new,'',-1,@fieldname)
print @sql
--检查并完成删除处理
declare @value_code varchar(50),
@value_code_child varchar(50)
select @value_code=QUOTENAME(@code,N''''),
@value_code_child=QUOTENAME(@code+N'_%',N'''')
print @value_code
print @value_code_child
select no_old=bh,
no_new= case
when len(bh)<1 then ''
else substring(bh,1,2)
end
+case
when len(bh)<6 then ''
else left(substring(bh,6,4),3)
end
into #
from hzfa with(xlock,tablock)
where bh like '01001_%'
delete from hzfa
where bh='01001'
update a set bh=b.no_new
from hzfa a,# b
where a.bh=b.no_old
select * from hzfa
--结果
1 01
3 01100
4 01200
5 01300
6 02
7 02002
8 020020002
9 03
10 03003
11 030030003
if object_id('hzfa') is not null drop table hzfa
create table hzfa(id int identity,bh varchar(100))
insert hzfa(bh)
select '01'
union all select '01001'
union all select '010010001'
union all select '02'
union all select '02002'
union all select '020020002'
union all select '03'
union all select '03003'
union all select '030030003'