sql try/catch rollback/commit - preventing erroneous commit after rollback

I always thought this was one of the better articles on the subject. It includes the following example that I think makes it clear and includes the frequently overlooked @@trancount which is needed for reliable nested transactions

PRINT 'BEFORE TRY'BEGIN TRY    BEGIN TRAN     PRINT 'First Statement in the TRY block'     INSERT INTO dbo.Account(AccountId, Name , Balance) VALUES(1, 'Account1',  10000)     UPDATE dbo.Account SET Balance = Balance + CAST('TEN THOUSAND' AS MONEY) WHERE AccountId = 1     INSERT INTO dbo.Account(AccountId, Name , Balance) VALUES(2, 'Account2',  20000)     PRINT 'Last Statement in the TRY block'    COMMIT TRANEND TRYBEGIN CATCH    PRINT 'In CATCH Block'    IF(@@TRANCOUNT > 0)        ROLLBACK TRAN;    THROW; -- raise error to the clientEND CATCHPRINT 'After END CATCH'SELECT * FROM dbo.Account WITH(NOLOCK)GO

In your first example, you are correct. The batch will hit the commit transaction, regardless of whether the try block fires.

In your second example, I agree with other commenters. Using the success flag is unnecessary.

I consider the following approach to be, essentially, a light weight best practice approach.

If you want to see how it handles an exception, change the value on the second insert from 255 to 256.

CREATE TABLE #TEMP ( ID TINYINT NOT NULL );INSERT  INTO #TEMP( ID ) VALUES  ( 1 )BEGIN TRY    BEGIN TRANSACTION    INSERT  INTO #TEMP( ID ) VALUES  ( 2 )    INSERT  INTO #TEMP( ID ) VALUES  ( 255 )    COMMIT TRANSACTIONEND TRYBEGIN CATCH    DECLARE         @ErrorMessage NVARCHAR(4000),        @ErrorSeverity INT,        @ErrorState INT;    SELECT         @ErrorMessage = ERROR_MESSAGE(),        @ErrorSeverity = ERROR_SEVERITY(),        @ErrorState = ERROR_STATE();    RAISERROR (        @ErrorMessage,        @ErrorSeverity,        @ErrorState            );    ROLLBACK TRANSACTIONEND CATCHSET NOCOUNT ONSELECT IDFROM #TEMPDROP TABLE #TEMP

I used below ms sql script pattern several times successfully which uses Try-Catch,Commit Transaction- Rollback Transaction,Error Tracking.

Your TRY block will be as follows

 BEGIN TRY BEGIN TRANSACTION T ---- //your script block ---- COMMIT TRANSACTION T  END TRY

Your CATCH block will be as follows

BEGIN CATCHDECLARE @ErrMsg NVarChar(4000),         @ErrNum Int,         @ErrSeverity Int,         @ErrState Int,         @ErrLine Int,         @ErrProc NVarChar(200) SELECT @ErrNum = Error_Number(),        @ErrSeverity = Error_Severity(),        @ErrState = Error_State(),        @ErrLine = Error_Line(),        @ErrProc = IsNull(Error_Procedure(), '-') SET @ErrMsg = N'ErrLine: ' + rtrim(@ErrLine) + ', proc: ' + RTRIM(@ErrProc) + ',        Message: '+ Error_Message()

Your ROLLBACK script will be part of CATCH block as follows

IF (@@TRANCOUNT) > 0 BEGINPRINT 'ROLLBACK: ' + SUBSTRING(@ErrMsg,1,4000)ROLLBACK TRANSACTION TENDELSEBEGINPRINT SUBSTRING(@ErrMsg,1,4000);   ENDEND CATCH

Above different script blocks you need to use as one block. If any error happens in the TRY block it will go the the CATCH block. There it is setting various details about the error number,error severity,error line ..etc. At last all these details will get append to @ErrMsg parameter. Then it will check for the count of transaction (@@TRANCOUNT >0) , ie if anything is there in the transaction for rollback. If it is there then show the error message and ROLLBACK TRANSACTION. Otherwise simply print the error message.

We have kept our COMMIT TRANSACTION T script towards the last line of TRY block in order to make sure that it should commit the transaction(final change in the database) only after all the code in the TRY block has run successfully.