Skip to content Skip to sidebar Skip to footer

Why Rollback Is Not Working For Variable Table In Sql Server 2012?

I have created one variable table. In my stored procedure, there are lots of transactions. Now whenever an error occurs, I want to rollback a specific transactions which has some

Solution 1:

You are not using a temp table, you are using a variable table. There is a difference.

Temp tables work with transactions, variable tables don't. See http://blog.sqlauthority.com/2009/12/28/sql-server-difference-temp-table-and-table-variable-effect-of-transaction/

If you were to change your variable table @tab to a temporary table of #tab, you would get your desired behavior.

Differences between temp and variable tables: https://dba.stackexchange.com/questions/16385/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server/16386#16386

I have modified my question. Thanks for your knowledge sharing. But question remains the same. Why it is not working for variable table?

The links I posted above go through that with more detail than I could.

Post a Comment for "Why Rollback Is Not Working For Variable Table In Sql Server 2012?"