david.holley (8/30/2012)

---

Example 2:
BEGIN TRANSACTION 1
DELETE...
BEGIN TRANSACTION 2
UPDATE...
BEGIN TRANSACTION 3
INSERT
COMIT TRANSACTION 3
COMIT TRANSACTION 2
COMIT TRANSACTION 1

david.holley (8/30/2012)

---

So basically a single transaction and then check @@Error after each statement as in...

BEGIN TRAN

UPDATE
IF @@ERROR <> 0 GOTO ERR_HANDLER

DELETE
IF @@ERROR <> 0 GOTO ERR_HANDLER

DESTROY WORLD
IF @@ERROR <> 0 GOTO ERR_HANDLER

INSERT
IF @@ERROR <> 0 GOTO ERR_HANDLER

COMMIT TRAN

RETURN 0

ERR_HANDLER:
KILL KENNY
ROLLBACK
RETURN 1

BEGIN TRAN

begin try
	UPDATE
	DELETE
	DESTROY WORLD
	INSERT
	COMMIT TRAN
	RETURN 0
end try

begin catch
	KILL KENNY
	ROLLBACK 
	RETURN 1
end catch

Sean Lange (8/30/2012)

---

That is not the best way to handle this. You should use try/catch instead the older style of constantly checking @@error.

BEGIN TRAN

begin try
	UPDATE
	DELETE
	DESTROY WORLD
	INSERT
	COMMIT TRAN
	RETURN 0
end try

begin catch
	KILL KENNY
	ROLLBACK 
	RETURN 1
end catch