My Technical Notes

Friday, 21 June 2013

SQL Server Transactions Template Code

The way you would use the following code, is to replace the replace the PRINT statements with useful work and use RAISERROR in situations which are invalid. If an exception is thrown during execution then all the work done would be rolled back. Note that the statement SET XACT_ABORT ON changes the behaviour of how the transaction is handled.


BEGIN TRY
    SET XACT_ABORT ON

    BEGIN TRANSACTION
    
        IF 1 = 1
        BEGIN
            RAISERROR ('', 16, 1)
        END
        ELSE
        BEGIN
            PRINT 'do work here'
        END
     
        IF 1 <> 0
        BEGIN
            RAISERROR ('', 16, 1)
        END
        ELSE
        BEGIN
            PRINT 'do other work here'
        END
     
    COMMIT TRANSACTION
END TRY

BEGIN CATCH
    ROLLBACK TRANSACTION
END CATCH

No comments: