SQL transaction in try... catch will not roll back

Question

I'm learning to use Try... Catch and Transactions in SQL and im trying to test this out on some data that I have. From what I've learned if one execution fails, then the catch should rollback the entire batch so none of the tables in the TRY section should be updated, HOWEVER, when i run this, it will throw the error for the @b table in the catch block, but it will still insert the values for the @a table

SET XACT_ABORT ON;

Begin Try
  Begin Transaction s1
    Insert Into @a
      Values(@CountryId,@Country,@CountryCode,@DisplayImage,0,@ContinentCode,40)
    Insert Into @b
      Values('xxxxx',@ContinentCode,@Continent,0) --ERROR   
  Commit Transaction
End Try
Begin Catch
  Select ERROR_NUMBER(),ERROR_MESSAGE()
  Rollback Transaction s1
End Catch

select * from @a
select * from @b

The CATCH BLOCK runs, and I see the error_number and error_message, but shouldn't the first insert get rolled back?

Amir Keshavarz Amir Keshavarz · Accepted Answer · 2014-01-06T15:18:56

Variables and table variables are not part of data modificaion, so they are not rollback. rollback or save in transactions are working for data modification. in your code use temp tables instead of table variables. It is gonna work.

SQL transaction in try... catch will not roll back

1 Answers