BEGIN TRY
  BEGIN TRAN
  -- SQL 
  IF @@TRANCOUNT > 0 COMMIT TRAN

END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0 ROLLBACK TRAN
  SET @RetVal = 1
  SET @ErrorMessage   = ERROR_MESSAGE()
  EXEC msdb..sp_send_dbmail 
    @profile_name          = 'default profile',
    @recipients            = @recipients      ,
    @subject               = @subject         , 
    @importance            = @importance      ,
    @body_format           = 'HTML'           ,
    @body                  = @ErrorMessage    ,

END
---
RETURN @RetVal