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