SQL Server : don't send email if transaction rolls back

Viewed 923

I have a piece of code that uses TRY / CATCH to roll back a transaction if an INSERT fails. However, in the middle of the code I have a piece of code that sends out an email per loop round.

The problem that I'm having is that the loop sends out the email and then makes an insert. So Let's say that after 9 processes/rounds, the 10th round fails to INSERT; then the whole transaction is rolled back, but the 9 emails have already been sent. How can I improve it so that the emails are not sent first? Or how to avoid any emails to be sent if a transaction fails?

Here is a simplified version of my code for testing purposes.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[something]
AS
BEGIN
    SET NOCOUNT, XACT_ABORT ON; 

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

    BEGIN TRY
        DECLARE @something INT

        DECLARE ROW_RES CURSOR FOR
            SELECT something 
            FROM something

        OPEN ROW_RES

        FETCH NEXT FROM ROW_RES INTO @something

        WHILE (@@fetch_status = 0) 
        BEGIN
            IF (@something > 0)
            BEGIN
                EXEC msdb.dbo.sp_send_dbmail
                     @profile_name = 'myprofile',
                     @from_address = 'do_not_reply@me.com',
                     @recipients = 'me@me.com',
                     @copy_recipients = 'ccme@me.com',
                     @subject = @email_subject,
                     @body = @email_body,
                     @body_format = 'html'
            END

            FETCH NEXT FROM ROW_RES INTO @something
        END

        IF (@something = false )
        BEGIN
            UPDATE something 
            SET something = (@something) 
            WHERE something = @something
        END
        ELSE IF (@something = true)
        BEGIN
            INSERT INTO something (something) 
            VALUES (@something)
        END

        COMMIT TRAN
    END TRY
    BEGIN CATCH
        DECLARE @MESSAGE NVARCHAR(2000), @SEVERITY INT, @STATE INT
        --See if there is a hanging transaction
        IF @@TRANCOUNT > 0 ROLLBACK TRAN
        --Rethrow the error to the calling application      
        SET @MESSAGE = ERROR_MESSAGE() 
        SET @SEVERITY = ERROR_SEVERITY() 
        SET @STATE = ERROR_STATE()
        RAISERROR(@MESSAGE, @SEVERITY, @STATE)      
    END CATCH

    SET NOCOUNT OFF;
END
CLOSE ROW_RES
DEALLOCATE ROW_RES
2 Answers
Related