Template for SQL Server 2005 Transaction

here is a template for a transaction in SQL Server (2005):

note: in SQL Server Management Studio, if you run this SQL without the COMMIT statement, then SQL Server will go ahead and commit the transaction anyway !


/* template for a SQL transaction in SQL Server *.


BEGIN TRAN myTran


{... my SQL statements here ...}
(... you can EXEC other SPs here ...)
(... you can have nested Transactions here, and also within the other SPs ...)


--note: in addition to checking ERROR, you may also need to check return codes from calls to other SQL objects (for example, stored procedures)


--xxx do something 1
IF @@ERROR <> 0
BEGIN
 GOTO ON_ERROR
END



--xxx do something 2
IF @@ERROR <> 0
BEGIN
 GOTO ON_ERROR
END





COMMIT TRAN myTran 


RETURN 0 

ON_ERROR:
BEGIN 
ROLLBACK TRAN myTran 
RAISERROR('my error message here', 18, 1) /* params are: message, severity, state */
 --note: use severity = 20 if you wish to KILL the SQL script - for example, instead of using RETURN
RETURN 1 
END