Hi. This should be an easy question, but I can't find anyone else addressing this same issue in the forums. I am writing a procedure that will perform updates inside of a loop for a batch operation. If any one of those updates fail, I need all updates to roll back and throw an error. I need the transaction to be defined inside of the procedure rather than defining a transaction and then executing the procedure inside of that transaction (I can't depend on the end-user to remember to execute the procedure inside of a transaction).
Below is a simplified example of the scenario to demonstrate. The 3rd and 5th record are setup to cause an error when the update occurs inside of the loop. As I understand it, it should roll back all 5 updates... but it doesn't. Why? The last select statement shows first 2 records updated and last 3 records did not. Can anyone show me what I'm doing wrong?
--Declare a table variable declare @Tbl table( Position int, Value varchar(30) ) --Insert some data Insert into @Tbl Select 1, '23232' union Select 2, '22342' union select 3, '34422a' union select 4, '66345' union select 5, '52342b' --Prepare variable for Loop declare @value varchar(30) declare @i int declare @numrows int --Perform loop operation inside of a transaction. Rollback whole transaction if any operation inside of loop fails. BEGIN TRY BEGIN TRANSACTION; SET @i = 1 SET @numrows = (SELECT COUNT(*) FROM @Tbl) IF @numrows > 0 WHILE (@i <= (SELECT MAX(Position) FROM @tbl)) BEGIN SET @value = (SELECT value FROM @Tbl WHERE Position = @i) --Update the Value w/ addition operation knowing that 3rd and 5th record will fail. update @tbl set value = cast(cast(@value as int) + 100000000000 as varchar(30)) where position = @i SET @i = @i + 1 END If (@@RowCount <> 1) -- Error condition Begin Raiserror('Error Message',16,1) End COMMIT TRANSACTION; END TRY BEGIN CATCH SELECT ERROR_NUMBER() as ErrorNumber, ERROR_MESSAGE() as ErrorMessage; -- Test XACT_STATE for 1 or -1. -- XACT_STATE = 0 means there is no transaction and -- a commit or rollback operation would generate an error. -- Test whether the transaction is uncommittable. IF (XACT_STATE()) = -1 BEGIN PRINT N'The transaction is in an uncommittable state. ' + 'Rolling back transaction.' ROLLBACK TRANSACTION; END; -- Test whether the transaction is active and valid. IF (XACT_STATE()) = 1 BEGIN PRINT N'The transaction is committable. ' + 'Committing transaction.' COMMIT TRANSACTION; END; END CATCH; -- Check the table variable to see what records were updated Select * from @Tbl