Here is a simple Transaction template for Sql Server 2005+
: – 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!