[dbo].[EmailApiMarkFailures]
CREATE PROCEDURE [dbo].[EmailApiMarkFailures]
@inOutgoingEmailQueueIds IntList readonly,
@inText nvarchar(max),
@inRequestTraceId varchar(64),
@inAttemptedAt datetimeoffset,
@inSentAt datetimeoffset = null,
@inCompletedAt datetimeoffset = null
AS
begin
set nocount on
declare @batchFailureId int
insert into OutgoingEmailBatchFailure(
BodyOrMessage,
AttemptedAt,
SentAt,
CompletedAt,
RequestTraceId
)
select
BodyOrMessage = @inText,
@inAttemptedAt,
@inSentAt,
@inCompletedAt,
@inRequestTraceId
set @batchFailureId = SCOPE_IDENTITY()
insert into OutgoingEmailFailure(
OutgoingEmailFID,
OutgoingEmailBatchFailureFID,
EnqueuedAt
)
select
OutgoingEmail.OutgoingEmailID,
@batchFailureId,
OutgoingEmailQueue.EnqueuedAt
from @inOutgoingEmailQueueIds EmailQueueIds
inner join OutgoingEmailQueue on EmailQueueIds.Item = OutgoingEmailQueue.OutgoingEmailQueueID
inner join OutgoingEmail on OutgoingEmailQueue.OutgoingEmailFID = OutgoingEmail.OutgoingEmailID
order by EmailQueueIds.Item asc
exec SyncOutgoingEmailStatus
@inOutgoingEmailQueueIds = @inOutgoingEmailQueueIds,
@inActivityTimeStamp = @inAttemptedAt,
@inSucceeded = 0
delete OutgoingEmailQueue
from @inOutgoingEmailQueueIds EmailQueueIds
inner join OutgoingEmailQueue on EmailQueueIds.Item = OutgoingEmailQueue.OutgoingEmailQueueID
end
GO
GRANT EXECUTE ON [dbo].[EmailApiMarkFailures] TO [MssExec]
GO