if exists(select * from dbo.sysobjects
where id=object_id('contentCopyChannerId') and objectproperty(id,'isprocedure')=1)
drop procedure contentCopyChannerId
go
create proc contentCopyChannerId as
declare @ID numeric
declare @DETAILID numeric
declare @USERID numeric
declare @BASECHANNEL numeric
declare @NEWBASECHANNEL numeric
declare @TITLE varchar(300)
declare @CREATETIME datetime
declare @SOURCE varchar(100)
declare @SOURCEURL varchar(100)
declare @FINGER varchar(100)
declare @AUTH_TYPE numeric
declare @ATTACHMENT_COUNT numeric
declare @COMMENT_COUNT numeric
declare @HITS numeric
declare @displaytime datetime
declare @isdel numeric
declare @titlePicture numeric
declare @style varchar(20)
declare @titlecolor varchar(50)
declare @TOP char(1)
declare @TOPTIME datetime
declare @channel3g_Click int
declare @isCopy varchar(50)
declare @State int
declare @newID numeric
declare @CONTENT_CHANNEL_STATUS numeric
declare @CONTENT_CHANNEL_CREATETIME datetime
declare @CONTENT_CHANNEL_PUBLISHTIME datetime
Set @State = 0
declare @contentCursor cursor --内容
declare @contentChannelCursor cursor --内容频道关系
Begin Tran
set @contentCursor=cursor for
select top 2 [ID]
, DETAILID
, USERID
,( case BASECHANNEL
when '100339' then '101133'
when '100340' then '101134'
when '100343' then '101117'
when '100317' then '100985'
when '100319' then '100985'
when '100661' then '100984'
when '100311' then '100986'
when '100316' then '100981'
when '101111' then '101004'
when '100315' then '100982'
when '101110' then '101006'
when '100318' then '100990'
when '100342' then '101123'
when '100312' then '101120'
when '100627' then '101122'
end) as NEWBASECHANNEL
, BASECHANNEL
, TITLE
, CREATETIME
, SOURCE
, SOURCEURL
, FINGER
, AUTH_TYPE
, ATTACHMENT_COUNT
, COMMENT_COUNT
, HITS
, displaytime
, isdel
, titlePicture
, style
, titlecolor
, [TOP]
, TOPTIME
, channel3g_Click
from CONTENT
where BASECHANNEL in( --100339
--,100340
--,100343
--,100317
--,100319
--,100661
100311
--,100316
--,101111
--,100315
--,101110
--,100318
--,100342
--,100312
--,100627
) and (isCopy is null or isCopy='')
open @contentCursor
--如果没有任何行则直接退出
If @@Cursor_Rows = 0
Begin
Close @contentCursor
Deallocate @contentCursor
End
fetch next from @contentCursor into
@ID
, @DETAILID
, @USERID
, @NEWBASECHANNEL
, @BASECHANNEL
, @TITLE
, @CREATETIME
, @SOURCE
, @SOURCEURL
, @FINGER
, @AUTH_TYPE
, @ATTACHMENT_COUNT
, @COMMENT_COUNT
, @HITS
, @displaytime
, @isdel
, @titlePicture
, @style
, @titlecolor
, @TOP
, @TOPTIME
, @channel3g_Click
while(@@fetch_status=0)
begin
set @isCopy=1
insert into CONTENT(
DETAILID
, USERID
, BASECHANNEL
, TITLE
, CREATETIME
, SOURCE
, SOURCEURL
, FINGER
, AUTH_TYPE
, ATTACHMENT_COUNT
, COMMENT_COUNT
, HITS
, displaytime
, isdel
, titlePicture
, style
, titlecolor
, [TOP]
, TOPTIME
, channel3g_Click
, isCopy
)
values(
@DETAILID
, @USERID
, @NEWBASECHANNEL
, @TITLE
, @CREATETIME
, @SOURCE
, @SOURCEURL
, @FINGER
, @AUTH_TYPE
, @ATTACHMENT_COUNT
, @COMMENT_COUNT
, @HITS
, @displaytime
, @isdel
, @titlePicture
, @style
, @titlecolor
, @TOP
, @TOPTIME
, @channel3g_Click
, @isCopy
)
Set @newID = @@Identity
update CONTENT set isCopy='2'
where (isCopy is null or isCopy='') and ID=@ID
set @contentChannelCursor=cursor for --内容频道关系结果集
select CREATETIME, PUBLISHTIME, STATUS
from CONTENT_CHANNEL
where CHANNEL_ID=@BASECHANNEL
and CONTENT_ID=@ID
Open @contentChannelCursor
--如果没有任何行则直接退出
If @@Cursor_Rows = 0
Begin
Close @contentChannelCursor
Deallocate @contentChannelCursor
End
fetch next from @contentChannelCursor into @CONTENT_CHANNEL_CREATETIME, @CONTENT_CHANNEL_PUBLISHTIME, @CONTENT_CHANNEL_STATUS
Close @contentChannelCursor
Deallocate @contentChannelCursor
insert into CONTENT_CHANNEL(
CONTENT_ID
, CHANNEL_ID
, CREATETIME
, PUBLISHTIME
, STATUS
, isCopy
)values
(
@newID
,@NEWBASECHANNEL
,@CONTENT_CHANNEL_CREATETIME
,@CONTENT_CHANNEL_PUBLISHTIME
,@CONTENT_CHANNEL_STATUS
,'1'
)
update CONTENT_CHANNEL set isCopy='2'
where (isCopy is null or isCopy='')
and CHANNEL_ID=@BASECHANNEL
and CONTENT_ID=@ID
If @@Error <> 0
Begin
Rollback Tran
Close contentCursor
Deallocate contentCursor
Return -1
End
fetch next from @contentCursor into
@ID
, @DETAILID
, @USERID
, @NEWBASECHANNEL
, @BASECHANNEL
, @TITLE
, @CREATETIME
, @SOURCE
, @SOURCEURL
, @FINGER
, @AUTH_TYPE
, @ATTACHMENT_COUNT
, @COMMENT_COUNT
, @HITS
, @displaytime
, @isdel
, @titlePicture
, @style
, @titlecolor
, @TOP
, @TOPTIME
, @channel3g_Click
End
Close @contentCursor
Deallocate @contentCursor
Commit Tran
EXECUTE contentCopyChannerId