CODEWITHSUNDEEP
sql-server
Sin5k4
Sin5k4

Reputation: 1626

USING TRANSACTIONS IN ITERATIONS

AS in the code below,i'm trying to use transactions in an iteration.

    Declare @i int, @TRAN_NAME varchar(40)
Declare @TMPTABLE TABLE(pk int unique)
Set @i = 0

While @i < 5 Begin
      BEGIN TRY
            Set @TRAN_NAME = CONVERT(varchar(20), @i)
            BEGIN TRAN @TRAN_NAME
                  INSERT @TMPTABLE
                  VALUES(@i)
                  if @i = 3 Begin
                        INSERT @TMPTABLE
                        VALUES(@i)
                  End
            COMMIT TRAN @TRAN_NAME 
      END TRY
      BEGIN CATCH
            ROLLBACK TRAN @TRAN_NAME
            DECLARE @msg varchar(1000) = ERROR_MESSAGE() 

            RAISERROR(@msg,16,1)
      END CATCH
      Set @i = @i + 1
End

Select * from @TMPTABLE

The problem is,the query i get from the code below returns me the following results:

pk
0
1
2
3
4

But the problem is,since I am intentionally trying to insert a pk in the iteration I don't want it to insert the value and the value before it,as in the code,the results should not have a 3,since it will be rolled back by the transaction yet it still inserts 3.

Upvotes: 0

Views: 67

Answers (1)

podiluska
podiluska

Reputation: 51494

Transactions don't apply to in memory tables. If you use a temporary table #tmp it will work as you expect.

From http://technet.microsoft.com/en-us/library/ms175010.aspx

Because table variables have limited scope and are not part of the persistent database, they are not affected by transaction rollbacks.

See also http://www.sqlservercentral.com/blogs/steve_jones/2010/09/21/table-variables-and-transactions/

Upvotes: 1

Related Questions