Stored Procedures [dbo].[SyncOutgoingEmailStatus]
Properties
PropertyValue
ANSI Nulls OnYes
Quoted Identifier OnYes
Parameters
NameData TypeMax Length (Bytes)
@inOutgoingEmailQueueIdsIntListmax
@inActivityTimeStampdatetimeoffset10
@inSucceededbit1
Permissions
TypeActionOwning Principal
GrantExecuteMssExec
SQL Script
/*
* Synchronizes a "last activity" date
*/


CREATE PROCEDURE [dbo].[SyncOutgoingEmailStatus]
    @inOutgoingEmailQueueIds IntList readonly,
    @inActivityTimeStamp datetimeoffset,
    @inSucceeded bit = null
AS
begin
    set nocount on

    ;with [Data] as
    (
        select OutgoingEmailQueue.OutgoingEmailFID,
        [TimeStamp] = @inActivityTimeStamp,
        Succeeded = @inSucceeded
        from @inOutgoingEmailQueueIds QueueIds
        inner join OutgoingEmailQueue on QueueIds.Item = OutgoingEmailQueue.OutgoingEmailQueueID
    )
    merge OutgoingEmailStatus using [Data] on
        [OutgoingEmailStatus].OutgoingEmailFID = [Data].OutgoingEmailFID
    when not matched then
        insert
        (
            OutgoingEmailFID,
            LastActivity,
            Succeeded
        )
        values
        (
            [Data].OutgoingEmailFID,
            [Data].[TimeStamp],
            [Data].[Succeeded]
        )
    when matched then update set
        LastActivity = [TimeStamp],
        Succeeded = @inSucceeded
    ;


end
GO
GRANT EXECUTE ON  [dbo].[SyncOutgoingEmailStatus] TO [MssExec]
GO
Uses
Used By