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

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