SQL Server Table Variables gotcha–results persist after a rollback

An idea came over me last night…back over two years ago I wrote a few blogs on table variables, and was using one the other day. I remembered that they have a problem in some instances with transactions, and could possibly not be ‘rolled back’. It states in Books Online that the duration is short, but it doesn’t make mention of the possibility of persisting values if a rollback occurred. In the below example, you see that the value inserted into a table variable rolls back on an insert, but in this situation you can see that you would not want to SELECT or report from a table variable, or use one as an OUTPUT variable to a stored procedure. Let’s check it out. Run the following -  let’s create a table:

CREATE TABLE CheckingAccount

    (id int identity (1,1) NOT NULL PRIMARY KEY

    ,fkCustomerID int NOT NULL

    ,Balance money NOT NULL DEFAULT (0)

    ,LastTransDate datetime2 DEFAULT GETDATE()

    );

GO

Now we’ll create a stored procedure to set up the experiment:

CREATE PROCEDURE proc_InsertDeposit

         @fkCustomerID int

        ,@Amt money

        ,@AmtProcessed money OUTPUT

AS

SET NOCOUNT ON

BEGIN TRY

    BEGIN TRAN

    DECLARE @insertDeposit TABLE (fkCustomerID int, Amt money)

    INSERT @insertDeposit values (@fkCustomerID, @Amt)

    -- DO SOME OTHER PROCESSING

    INSERT INTO CheckingAccount (fkCustomerID, Balance)

    SELECT fkCustomerID, Amt

    FROM @insertDeposit

    ROLLBACK

    SELECT @AmtProcessed = Amt from @insertDeposit

END TRY

BEGIN CATCH

    SELECT ERROR_MESSAGE()

    ROLLBACK TRAN

END CATCH         

GO

Notice that if you run this procedure the rollback will remove the record from the CheckingAccount table via the ROLLBACK. However, the @AmtProcessed will get populated and not roll back because the ROLLBACK will not affect the table variable @insertDeposit.  That’s the good news – I was thinking that it may actually not roll this back because the value is still available in the table variable. As you can see, however, I have an output variable based on the value @AmountProcessed; this will get returned even though the entire transaction has been rolled back and exited. Not good. Run the following:

SELECT * FROM CheckingAccount;

GO     

DECLARE @AmountTransactionProcessed money

EXEC dbo.proc_InsertDeposit @fkCustomerID = 1, @Amt =5000,  @AmtProcessed 
                         = @AmountTransactionProcessed OUTPUT

SELECT @AmountTransactionProcessed

GO

SELECT * FROM CheckingAccount;

GO     

So, for this example, it’s pretty easy to see that this isn’t a great way to get a return value. Let’s look at SQL Server Books Online for SQL Server 2008 R2 and look at example c in the reference to DECLARE @local_variable (Transact-SQL). Make the change to their code by adding a BEGIN TRAN as I have done here. I have added the code to mimic the table in the AdventureWorks database in case you do not have that installed on your computer or workstation:

CREATE DATABASE AdventureWorks

GO

USE AdventureWorks

go

CREATE SCHEMA HumanResources

go

DROP TABLE HumanResources.Employee

GO

CREATE TABLE HumanResources.Employee

    (EmpID int

    ,BusinessEntityID int

    ,VacationHours int

    ,ModifiedDate datetime

    )

GO

INSERT HumanResources.Employee

SELECT 1,100,20,GETDATE()

GO

BEGIN TRAN

DECLARE @MyTableVar table(

    EmpID int NOT NULL,

    OldVacationHours int,

    NewVacationHours int,

    ModifiedDate datetime);

UPDATE TOP (10) HumanResources.Employee

SET VacationHours = VacationHours * 1.25,

    ModifiedDate = GETDATE() 

OUTPUT inserted.BusinessEntityID,

       deleted.VacationHours,

       inserted.VacationHours,

       inserted.ModifiedDate

INTO @MyTableVar;

ROLLBACK

--Display the result set of the table variable.

SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate

FROM @MyTableVar;

GO

--Display the result set of the table.

SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate

FROM HumanResources.Employee;

GO

In both examples, the value or values from the table variable persists beyond the end of the transaction rollback and is reported, although we know that it this case it becomes invalid. Be careful using output values with table variables in your coding.

Happy Table Variabling!

Lee Everest

------------------------

A picture of my ex-wife for you