CREATE PROCEDURE [dbo].[MssWebInsertUpdateRecentlyUsedEmail]
    @inRecentlyUsedEmailAddress MssWebRecentlyUsedEmailAddress READONLY,
    @inSenderSysUserFid int
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @lastUsedOn DATETIMEOFFSET = SYSDATETIMEOFFSET();

    MERGE RecentlyUsedEmailAddress AS theTarget
    USING
    (
        SELECT DISTINCT
               MAX(MRUEmails.RecipientName) AS RecipientName,
               MRUEmails.EmailAddress,
               @lastUsedOn AS lastUsedOn
        FROM @inRecentlyUsedEmailAddress MRUEmails
        GROUP BY MRUEmails.EmailAddress
    ) AS theSource
    ON theTarget.SenderSysUserFid = @inSenderSysUserFid
       AND theTarget.EmailAddress = theSource.EmailAddress
    WHEN MATCHED THEN
        UPDATE SET RecipientName = theSource.RecipientName,
                   LastUsedOn = @lastUsedOn
    WHEN NOT MATCHED THEN
        INSERT
        (
            SenderSysUserFid,
            RecipientName,
            EmailAddress,
            lastUsedOn
        )
        VALUES
        (@inSenderSysUserFid, theSource.RecipientName, theSource.EmailAddress, @lastUsedOn);

    WITH CTERecentlyUsedEmailAddress
    AS (SELECT RecentlyUsedEmailAddress.SenderSysUserFId,
               RowNum = ROW_NUMBER() OVER (ORDER BY lastUsedOn DESC),
               RecentlyUsedEmailAddress.EmailAddress
        FROM RecentlyUsedEmailAddress
        WHERE RecentlyUsedEmailAddress.SenderSysUserFId = @inSenderSysUserFid)
    DELETE RecentlyUsedEmailAddress
    FROM CTERecentlyUsedEmailAddress
        INNER JOIN RecentlyUsedEmailAddress
            ON CTERecentlyUsedEmailAddress.SenderSysUserFId = RecentlyUsedEmailAddress.SenderSysUserFId
               AND CTERecentlyUsedEmailAddress.EmailAddress = RecentlyUsedEmailAddress.EmailAddress
               AND CTERecentlyUsedEmailAddress.RowNum > 100;

END;
GO
GRANT EXECUTE ON  [dbo].[MssWebInsertUpdateRecentlyUsedEmail] TO [MssExec]
GO