create procedure dbo.pr_sync_subdata
as
begin

declare @v_etime varchar(14)
declare @v_btime varchar(14)
declare @v_ctime varchar(14)
declare @v_pname varchar(128)
declare @v_userid varchar(40)
declare @v_now varchar(14)
declare @v_id int

declare @v_status int
declare @v_catid varchar(14)
declare @v_synid int
declare @v_sub_status int
declare @v_batchid int
declare @v_delOrsaveBatchid int
declare @v_count int

---ȥ˽¾۠ǟ´̯ͬǰµ
exec sp_nextval 't_subscription_bak.batch
id',@v_batchid output
delete from t_subscription_bak where batchid<=@v_batchid-7
---±¸·ފ弴_subscription_bak
select @v_now=convert(varchar,convert(datetime,getdate()),112)+str_replace(conv
ert(varchar,getdate(),108),":",null)
insert into t_subscrip
tion_bak(id,userid,productcode,begintime,endtime,
creattime,purchasetype,subextend,unsub_time,status,fee,stbid,
areacode,pointsubscription,pointrate,catid,catid1,cpid,moneyid,point,batchid,ba
ktime) select id,
userid,productcode,begintime,endtime,creatt
ime,purchasetype,subextend,
unsub_time,status,fee,stbid,areacode,pointsubscription,pointrate,
catid,catid1,cpid,moneyid,point,@v_batchid,@v_now from t_subscription

declare cur_userid cursor for select userid,endtime,begintime,productname,s
tatus,ca
tid from t_syn_sub order by id
open cur_userid
fetch cur_userid into @v_userid,@v_etime,@v_btime,@v_pname,@v_status,@v_catid
--@@sqlstatus 2:½ẻ¼¯ºϲ»ՙԐ˽¾
--@@sqlstatus 1:fetchԯ¾厪³ɲ»³ɹ¦
--@@sqlstatus 0:fetchԯ¾厪³ɳɹ¦
set @v_cou
nt = 0
while @@sqlstatus<>2
begin
declare cur_max_id cursor for select top 1 id,status,creattime from t_subscrip
tion where userid=@v_userid and catid=@v_catid order by id desc
open cur_max_id
fetch cur_max_id into @v_id,@v_sub_status,@v_cti
me

while @@sqlstatus<>2
begin

--ͬ²½τ¼þאµŊ§Чʱ¼厭Ԛէ½͏µͳµĶ©¹ºʱ¼䣬´̊±¿ʒ԰´ͬ²½τ¼þµŊ±¼䷔է½
˽¾ݽ๼т
if @v_etime>@v_ctime
begin
update t_subscription set begintime=@v_btime,endtime=@v_etime where id=@v_id

end

--τ¼þאˇ΋¶©£¬է½͊Ƕ©¹º
--΋¶©»¹ѨҪúأһ¸�þ£¬¼´ͬ²½τ¼þאµŊ§Чʱ¼厭Ԛէ½͏µͳµĶ©¹ºʱ¼
if @v_etime>@v_ctime and @v_etime<@v_now and @v_sub_status=0
begin
---˽¾ݴ¦m
exec sp_nextval 't_syn_unsub',@v_synid output
ins
ert into t_syn_unsub(id,userid,createtime,status,proctimes,source,catid)
values(@v_synid,@v_userid,@v_now,0,0,1,@v_catid)
update t_subscription set status=1,unsub_time=@v_now where id=@v_id
end
---τ¼þאˇ¶©¹º£¬է½͊ȍ˶©
else if @v_et
ime>@v_now and @v_sub_status=1
begin
update t_subscription set status=0,unsub_time=null where id=@v_id
end

fetch cur_max_id into @v_id,@v_sub_status,@v_ctime
end
commit---
---¹رա¢ˍ·Ɠα
close cur_max_id
deallocate cursor cur_m
ax_id
fetch cur_userid into @v_userid,@v_etime,@v_btime,@v_pname,@v_status,@v_cat
id
set @v_count = @v_count + 1
if @v_count >= 100
begin
commit
set @v_count = 0
end
end
---¹رա¢ˍ·Ɠα
close cur_userid
deal
locate cursor cur_userid
commit
end