SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
drop PROCEDURE remove_repeated_msisdn
go
-- =============================================
-- 处理重复号
-- =============================================
CREATE PROCEDURE [dcms_zhq].remove_repeated_msisdn
AS
BEGIN
declare @num varchar(30)
declare @count bigint
declare @id bigint
declare @big_box varchar(30)
declare @little_box varchar(30)
declare @zha varchar(30)
declare @status bigint
declare c1 cursor local for
select msisdn_,count(id_) amount_ from dcms_zhq.SIM_CARD_RESOURCE_
where is_locked_='N' and unit_type_id_=400 and msisdn_='15119880662'
group by msisdn_
having count(id_)>1
open c1
fetch next from c1 into @num,@count
while (@@fetch_status=0)
begin
--#####################
declare c2 cursor local for
select id_,big_box_id_,little_box_id_,zha_id_,status_
from dcms_zhq.SIM_CARD_RESOURCE_ where msisdn_=@num order by input_date_time_ asc
declare @count2 bigint
set @count2 = 0
open c2
fetch next from c2 into @id,@big_box,@little_box,@zha,@status
while (@@fetch_status=0)
begin
set @count2 = @count2 + 1
if @count2<@count
begin
BEGIN TRANSACTION
update dcms_zhq.SIM_CARD_RESOURCE_ set
packer_=packer_+'_20100420_'+ltrim(rtrim(str(@count2))),
msisdn_=msisdn_+'_20100420_'+ltrim(rtrim(str(@count2))),
life_status_=102
where id_=@id
if @status=2 or @status=16
begin
if(len(@zha)>0) update dcms_zhq.SIM_CARD_RESOURCE_ set amount_=amount_-1 where packer_=@zha
if(len(@little_box)>0) update dcms_zhq.SIM_CARD_RESOURCE_ set amount_=amount_-1 where packer_=@little_box
if(len(@big_box)>0) update dcms_zhq.SIM_CARD_RESOURCE_ set amount_=amount_-1 where packer_=@big_box
end
IF @@ERROR>0
ROLLBACK TRANSACTION
else
COMMIT TRANSACTION
end
fetch next from c2 into @id,@big_box,@little_box,@zha,@status
end
close c2
deallocate c2
--#####################
fetch next from c1 into @num,@count
end
close c1
deallocate c1
END
GO