Because I don't use them much, its not always clear to me what's the "best way" of using a transaction. Sure we all know the basics but:
- Is the transaction always closed?
- Are we handling the error that caused the transaction to rollback?
- Are we accurately reporting the error to the caller?
BEGIN TRY BEGIN TRANSACTION -- ADD YOUR CODE HERE -- IF @@TRANCOUNT > 0 BEGIN COMMIT TRANSACTION; END END TRY BEGIN CATCH DECLARE @ErrorMessage VARCHAR(4000) SET @ErrorMessage = 'ErrorProcedure: ' + ISNULL(ERROR_PROCEDURE(), '') + ' Line: ' + CAST(ERROR_LINE() AS VARCHAR(10)) + ' Message: ' + ERROR_MESSAGE() IF @@TRANCOUNT > 0 BEGIN ROLLBACK TRANSACTION; END RAISERROR (@ErrorMessage, 16, 1) END CATCH
You may change the way you report the error inside the CATCH but for most cases this is what you need.