一个对自己来说有记念意义的事务

begin tran aa ---

set nocount on

       declare @userid varchar(50) 

       declare @newcourseid varchar(50) 

       declare @newcourseid2 varchar(50) 

       declare @oldcourseid varchar(50)

       declare @subjectid varchar(6)

       declare @choosebook float 

       declare @postwayid   int

       declare @reValue int

       declare @i int

       set @i = 0

       set @reValue = 0

       set @oldcourseid = 'ZK012' --大学语文（第一版）

       set @newcourseid = 'ZK012B'--大学语文（第二版）

 

       declare cur_upgrade cursor for

              select --top 600

              s.userid,left(s.courseid,6) subjectid,s.hasbook,s.postwayid

              from studentcoursesys s

              inner join #temp3 t on s.userid = t.userid and s.courseid = t.courseid

              where right(t.courseid,6)='ZK012A' --order by s.userid

       open cur_upgrade

       fetch next from cur_upgrade into @userid,@subjectid,@choosebook,@postwayid

        while @@fetch_status = 0

        begin

              set @newcourseid2 = cast(@subjectid as varchar(6)) + cast(@newcourseid as varchar(6))

              execute @reValue = sp_change_course @userid,@newcourseid2,@oldcourseid,@choosebook,@postwayid

              if @reValue <>0

              begin

                     goto err

              end

             

              fetch next from cur_upgrade into @userid,@subjectid,@choosebook,@postwayid

              set @i = @i + 1

              print @i

       end

 

goto succ

 

err:

       rollback tran aa

       print cast(@reValue as char(20))+'失败!'    

       print @userid

       print @newcourseid2

       close cur_upgrade

       deallocate cur_upgrade

       return

 

succ:      

       close cur_upgrade

       deallocate cur_upgrade

       print cast(@i as char(5))

       print cast(@reValue as char(20))+'成功!'    

       --commit tran aa

       return