
CREATE PROCEDURE BadInsert
AS
DECLARE @myError int
BEGIN TRAN
INSERT INTO pubs..jobs 
VALUES('add headlines', 3, 20)
COMMIT TRAN

BEGIN TRY
EXEC BadInsert;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0 ROLLBACK TRAN
PRINT 'error '
+ CAST(ERROR_NUMBER() AS VARCHAR(4))
PRINT 'severity '
+ CAST(ERROR_SEVERITY() AS VARCHAR(5))
PRINT'state '
+ CAST(ERROR_STATE() AS VARCHAR(5))
PRINT ERROR_MESSAGE()
PRINT 'procedure '
+ ISNULL(ERROR_PROCEDURE(), 'BATCH')
PRINT 'line ' + CAST(ERROR_LINE() as VARCHAR(5))
END CATCH

SET XACT_ABORT OFF

BEGIN TRY
BEGIN TRAN
INSERT INTO pubs..jobs 
VALUES('adverts1', 10, 20)
INSERT INTO pubs..jobs 
VALUES('adverts2', 3, 20)
COMMIT TRAN
END TRY
BEGIN CATCH
IF XACT_STATE() = -1 ROLLBACK TRAN
ELSE COMMIT TRAN
END CATCH

ROLLBACK TRAN

select  @@TRANCOUNT

select * from pubs..jobs



















