The myth is to roll back the work done inside the transaction, in case of any error during the transaction.
However this is not the case with the SQL Server table variable, table type and variables. That is,
the changes made on these types are not rolled back even when there is an error.
Explore this with an example
In order to distinguish variable from table, I have created one temporary table and local variable. Inside the transaction, value 1 is assigned to the variable then 256. Assigning of value 256 will cause error as the variable data type is tinyint. The same applied for the temporary table. Due to the error, the transaction enter into the roll back section and the entire transaction rolled back successfully.
However by running the select statement “select @num as num” value 1 is return whilst “select * from #tbl” return nothing as expected. This clearly shows that there is no effect for local variable even though the transaction rolled back.
use tempdb GO create table #tbl (c1 tinyint) declare @num tinyint begin tran set @num = 1 set @num = 256 insert into #tbl (c1) values(1) insert into #tbl (c1) values(256) if @@ERROR =0 commit tran else rollback tran select @num as num select * from #tbl
The same is applicable for table variable and table type. Sample using table variable:
declare @tbl as table (c1 int) create table #tbl (c1 int) begin tran insert into @tbl (c1) values (1) insert into @tbl (c1) values (1/0) insert into #tbl (c1) values(1) insert into #tbl (c1) values(1/0) if @@ERROR =0 commit tran else rollback tran select * from @tbl select * from #tbl
Sample using table type:
create type mytab as table(c1 tinyint) GO declare @mytab as mytab begin tran insert into @mytab(c1) values (1) insert into @mytab(c1) values (256) if @@ERROR = 0 commit tran else rollback tran select * from @mytab