实际项目自己写的一个主从表带事物存储过程

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


 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值