CODEWITHSUNDEEP
sqlsql-server-2005transactions
Scott Chamberlain
Scott Chamberlain

Reputation: 127563

How to prevent future batches from completing on error and roll back?

If I have the following SQL query

CREATE TABLE #t1
    (a INT NOT NULL PRIMARY KEY);
SET XACT_ABORT ON
go

BEGIN TRANSACTION
INSERT INTO #t1 VALUES (1);
INSERT INTO #t1 VALUES (2);
INSERT INTO #t1 VALUES (3);
INSERT INTO #t1 VALUES (2); -- PK violation error
go

INSERT INTO #t1 VALUES (4);
go

COMMIT TRANSACTION

SET XACT_ABORT OFF

When I run the query I get this output

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected) Msg 2627, Level 14, State 1, Line 7 Violation of PRIMARY KEY constraint 'PK_#t1______66D536B1'. Cannot insert duplicate key in object 'dbo.#t1'.

(1 row(s) affected) Msg 102, Level 15, State 1, Line 8 Incorrect syntax near '#t1'.

And the value of #t1 is

a
-----------
4

What do I need to do so the script will abort out and not run the second batch?

EDIT:

Tried JNK's solution

begin try
BEGIN TRANSACTION
INSERT INTO #t1 VALUES (1)
INSERT INTO #t1 VALUES (2)
INSERT INTO #t1 VALUES (3)
INSERT INTO #t1 VALUES (2) -- PK violation error
go

INSERT INTO #t1 VALUES (4)
go

COMMIT TRANSACTION

end try
begin catch 
IF @@trancount > 0 Rollback

END catch

select * from #t1

truncate table #t1

However I get this output

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ')'.

(1 row(s) affected)
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'begin'.
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near 'END'.

And the table still contains a 4 afterward

EDIT2:

the errors with the try catch is because of the GO statement, which I need to have for my real script instead of this test case. so it appears try/catch will not work for this case.

Upvotes: 0

Views: 165

Answers (2)

clyc
clyc

Reputation: 2450

begin try
   begin transaction

    INSERT INTO #t1 VALUES (1);
    INSERT INTO #t1 VALUES (2);
    INSERT INTO #t1 VALUES (3);
    INSERT INTO #t1 VALUES (2); 
    INSERT INTO #t1 VALUES (4);

   commit
end try
begin catch
    if @@trancount > 0
        rollback

    declare @errmsg nvarchar(4000), @errseverity int
    select @errmsg = error_message(), @errseverity = error_severity()

    raiserror(@errmsg, @errseverity, 1)
end catch

From MSDN: " interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO.

A Transact-SQL statement cannot occupy the same line as a GO command. However, the line can contain comments. "

Upvotes: 0

JNK
JNK

Reputation: 65157

Add error handling:

BEGIN TRY
<your query>
END TRY
BEGIN CATCH

IF @@TRANCOUNT > 0 ROLLBACK    

<other error reporting processes>

END CATCH

The ROLLBACK is optional and will undo any inserts that completed before the error occured. You can also add multiple transactions, so if say the first 3 inserts are independent of some other stuff later on, wrap those in a transaction and commit it, then have another tran later on for the unrelated process.

Upvotes: 1

Related Questions