Basic Transaction Template

Here is a simple Transaction template for Sql Server 2005+

BEGIN TRANSACTION;
BEGIN TRY
: – code that could throw an error here!
END TRY
BEGIN CATCH
    EXEC RethrowError @@ProcID
    IF (@@TRANCOUNT > 0)
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
 

The RethrowError is a stored procedure call. This stored procedure would then take the error details and throw an error, though you might want to name the stored procedure differectly!

The RethrowError stored procedure will then copy the values from the ERROR_* functions to variables and call RAISERROR with the appropriate values. It essentially saves you doing the same thing in the catch block!

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: