SQL Transaction Processing

SQL Server is a transactional system, this is one of it's strengths.  Each statement that adds, updates or deletes data is logged as a transactional.  This is a feature that's worth reading up on, if you are not familiar.

To take advantage of this in your Transact-SQL code you need to use the Commit/Rollback statements.  By themselves they work ok but the real power is when you have a set of statements that need to be committed together - if any statement fails all statements fail.  Otherwise SQL will fail the statement that doesn't work and execute the others that do work.  This could make things very difficult depending on what you are doing, and that is where this feature comes into play.

There are a set of statements required to take advantage of this.  Again there are plenty of sources around the internet and from Microsoft that cover this.  I wanted to record these here for reference.


SET XACT_ABORT ON;
SET NOCOUNT ON;

SET LOCK_TIMEOUT 5000
BEGIN TRAN;
BEGIN TRY
-- sql statements in transaction go here
COMMIT TRAN;
END TRY
BEGIN CATCH

 ROLLBACK TRAN;
 -- optional EXEC spRaiseError;
END CATCH;
SET LOCK_TIMEOUT -1;