1

Here's my code. It's a very simple: the 1st INSERT fails while the last two succeed.

Before I go on, I want to clarify that I understand that I can plug in an IF statement with @@ERROR or BEGIN ROLLBACK that will solve the issue. I know how the issue can be solved.

Since both INSERTs in the script are between a BEGIN/COMMIT TRAN, I thought that this would be considered a single transaction. So in my case, since the 1st INSERT fails, then I assumed that the following INSERTs wouldn't executed or wouldn't be committed. It seems that's not the case.

Is the purpose of BEGIN TRAN and COMMIT TRAN to be able to use a the @@ERROR function and/or ROLLBACK TRAN between them?

IF OBJECT_ID('tempdb..#testTable') IS NOT NULL DROP TABLE #testTable
create table #testTable
(
    Id int not null,
    Name varchar(100)
)

BEGIN TRAN T1;
insert into #testTable select null, 'Joe';
-- select @@ERROR
if @@ERROR = 0
BEGIN
    insert into #testTable select 1, 'Bob';
    insert into #testTable select 2, 'Ralph';
END
COMMIT TRAN T1;
select * From #testTable

Thanks.

fdkgfosfskjdlsjdlkfsf
  • 3,165
  • 2
  • 43
  • 110

2 Answers2

3

Table variables (@testTable) do NOT support transactions.

If you need transactional support, use a normal temp table (#testTable) instead.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Is there some documentation for this? – rory.ap Feb 12 '16 at 16:44
  • 3
    @roryap - https://msdn.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." – Sean Lange Feb 12 '16 at 16:45
  • 2
    Thanks, @SeanLange! (I was searching - you were finding :-) ) – marc_s Feb 12 '16 at 16:47
  • I did not know that. Thanks. – fdkgfosfskjdlsjdlkfsf Feb 12 '16 at 17:03
  • I changed to temp tables, but the last two `INSERT`s are done, even though the first one fails. I would still need to use `@@error`. – fdkgfosfskjdlsjdlkfsf Feb 12 '16 at 17:04
  • 2
    @rbhatup If you want to do things differently when there are errors you need to use try/catch. – Sean Lange Feb 12 '16 at 17:06
  • @SeanLange, as I mentioned in my initial message, fixing the issue is not reason for the message. I thought that since it's between a BEGIN TRAN and END TRAN, it would be considered a single unit and either it all succeeds or all fails. – fdkgfosfskjdlsjdlkfsf Feb 12 '16 at 17:11
  • A transaction is NOT a try/catch block. When an error occurs in a block of code it will attempt to continue to the next statement. This is why the try/catch was introduced in sql 2005. – Sean Lange Feb 12 '16 at 17:13
  • I understand what you mean. I'm just saying that I thought a BEGIN/END TRAN was a way of taking several transactions (ie. INSERTS) and "converting" them into one unit that either succeeds or fails. – fdkgfosfskjdlsjdlkfsf Feb 12 '16 at 17:19
  • 1
    See the discussion here: http://stackoverflow.com/questions/1749719/sql-server-transactions-roll-back-on-error to see under what condition a transaction will roll back automatically (which is what I think you mean by single unit of failure). – Stuart Ainsworth Feb 12 '16 at 17:28
  • @StuartAinsworth, thanks for the link. I guess my question should be that, if the block of code doesn't have a ROLLBACK, then there's no reason to use transactions. – fdkgfosfskjdlsjdlkfsf Feb 12 '16 at 18:56
2

To make this series inserts an "all or nothing" situation you need to use TRY/CATCH

IF OBJECT_ID('tempdb..#testTable') IS NOT NULL DROP TABLE #testTable
create table #testTable
(
    Id int not null,
    Name varchar(100)
)

begin try
    BEGIN TRAN T1;
        insert into #testTable select 1, 'Bob';
        insert into #testTable select null, 'Joe';
        insert into #testTable select 2, 'Ralph';
    COMMIT TRAN T1;
end try

begin catch
    select 'Error encountered. No rows added'
    rollback transaction
end catch

select * From #testTable
Sean Lange
  • 33,028
  • 3
  • 25
  • 40