SQL show_abort_errors Code Library

USE pubs
go

if exists (select * from sysobjects where name='SHOW_ERROR' and type='U')
        DROP TABLE SHOW_ERROR
go

CREATE TABLE SHOW_ERROR
(
col1        smallint NOT NULL PRIMARY KEY,
col2        smallint NOT NULL
)
go

BEGIN TRANSACTION

INSERT SHOW_ERROR VALUES (1,1)
INSERT SHOW_ERROR VALUES (1,2)
INSERT SHOW_ERROR VALUES (2,2)

COMMIT TRANSACTION
go

SELECT * FROM SHOW_ERROR
go

/*

Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK__SHOW_ERROR__3B40CD36'. Cannot insert duplicate key in object 'SHOW_ERROR'.
The statement has been terminated.

col1        col2
------        ------
1                 1
2                 2

Here's a modified version of the transaction you've just seen. This example does some simple error checking using the built-in global variable @@error, and rolls back the transaction if any statement results in an error. In this example, no rows are inserted.
*/

if exists (select * from sysobjects where name='SHOW_ERROR' and type='U')
        DROP TABLE SHOW_ERROR
go

CREATE TABLE SHOW_ERROR
(
col1        smallint NOT NULL PRIMARY KEY,
col2        smallint NOT NULL
)
go

BEGIN TRANSACTION
INSERT SHOW_ERROR VALUES (1,1)
        if @@error <> 0 GOTO TRAN_ABORT
INSERT SHOW_ERROR VALUES (1,2)
        if @@error <> 0 GOTO TRAN_ABORT
INSERT SHOW_ERROR VALUES (2,2)
        if @@error <> 0 GOTO TRAN_ABORT
COMMIT TRANSACTION
-- GOTO ENDIT

TRAN_ABORT:
ROLLBACK TRANSACTION

ENDIT:
go

SELECT * FROM SHOW_ERROR
go

-- Because quite a few people have handled errors in transactions erroneously,
-- and because it can be tedious to add a check for an error after every single command, version 6.5 added a new SET statement that will abort a transaction if any error is encountered during the transaction. (There is no WHENEVER statement in
-- Transact-SQL today, although such a feature would be very useful for situations like this.)
-- Using SET XACT_ABORT ON causes the entire transaction to be aborted and
-- rolled back if any error is encountered.
-- The default setting of this is OFF, which is consistent with pre-6.5 semantics, as well as with ANSI-standard behavior.
-- By setting the XACT_ABORT ON option on, we can now rerun the example that does not do error checking, and see that no rows were inserted.
if exists (select * from sysobjects where name='SHOW_ERROR' and type='U')
        DROP TABLE SHOW_ERROR
go

CREATE TABLE SHOW_ERROR
(
col1        smallint NOT NULL PRIMARY KEY,
col2        smallint NOT NULL
)
go

SET XACT_ABORT ON
BEGIN TRANSACTION

INSERT SHOW_ERROR VALUES (1,1)
INSERT SHOW_ERROR VALUES (1,2)
INSERT SHOW_ERROR VALUES (2,2)

COMMIT TRANSACTION
go

SET XACT_ABORT OFF
SELECT * FROM SHOW_ERROR
go