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