sql server - T-SQL error handling with try catch rollback, error rows were deleted -
i have following construct. noticed erroneous rows error number > 0 being deleted @ end of while loop. don't understand have gone wrong error catching. need second commit in catch section commit update of error number?
while @@fetch_status = 0 begin try begin transaction [...insert table here...] set @countrec = @countrec + @@rowcount update alarmtable set success = 0 recno = @recno commit transaction end try begin catch if @@trancount > 0 rollback transaction select @error = error_number() , @errormsg = error_message() update alarmtable set success = @error recno = @recno if @@trancount > 0 commit transaction end catch fetch next listofrecords @recno, @alarmcontent end /* while */ close listofrecords deallocate listofrecords delete alarmtable success = 0
removed commit transaction , error entries don't removed anymore. @kevchadders.
Comments
Post a Comment