Table variable and Table type in transaction

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.

 

Let me 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

 

0 comments:

Post a Comment