USP_FAF_NEWSLETTER_ADD_EDIT

CREATE procedure dbo.USP_FAF_NEWSLETTER_ADD_EDIT
            (
             @EmailListIDList varchar(2000), -- enabledlist

             @DisabledEmailList varchar(2000) = null,
             @UserID int = null,
             @EmailAddress nvarchar(255) = null,
             @FirstName nvarchar(255) = null,
             @LastName nvarchar(255) = null
            ) with execute as caller
AS
BEGIN
SET NOCOUNT ON;

    DECLARE @currentdate datetime
    SET @currentdate = getdate()

    IF ISNULL(@UserID,0) > 0
    BEGIN

        Update ES
        SET HasOpted = 1,
            updatedate = @currentdate
        FROM
            EmailList_Subscription ES
            INNER JOIN dbo.UFN_PARSE_STRING (@emaillistidlist,',') EL
            ON EL.element_value = ES.EmailListID AND ES.UserID = @UserID

        INSERT INTO EmailList_Subscription
        SELECT EL.element_value,@UserID,null,1,P.BackofficePeopleID,null,null,null,@currentdate
        FROM
            dbo.UFN_PARSE_STRING(@emaillistidlist,',') EL
            LEFT OUTER JOIN EmailList_Subscription ES
            ON EL.element_value = ES.EmailListID AND ES.UserID = @UserID
            LEFT OUTER JOIN dbo.BackOfficeSystemUsers P
            ON P.ClientUsersID = @userid AND P.[Current] = 1
            WHERE ES.ID IS NULL 

        IF ISNULL(@DisabledEmailList,'') > ''    
        Update ES
        SET HasOpted = 0,
            updatedate = @currentdate
        FROM
            EmailList_Subscription ES
            INNER JOIN dbo.UFN_PARSE_STRING (@DisabledEmailList,',') EL
            ON EL.element_value = ES.EmailListID AND ES.UserID = @UserID

    END

    IF ISNULL(@UserID,0) = 0 AND ISNULL(@EmailAddress,'') > ''
    BEGIN

        Update ES
        SET HasOpted = 1,
            updatedate = @currentdate
        FROM
            EmailList_Subscription ES
            INNER JOIN dbo.UFN_PARSE_STRING (@emaillistidlist,',') EL
            ON EL.element_value = ES.EmailListID AND ES.EmailAddress = @EmailAddress

        INSERT INTO EmailList_Subscription
        SELECT EL.element_value,null,null,1,null,@EmailAddress,@FirstName,@LastName,@currentdate
        FROM
            dbo.UFN_PARSE_STRING (@emaillistidlist,',') EL
            LEFT OUTER JOIN EmailList_Subscription ES
            ON EL.element_value = ES.EmailListID AND ES.EmailAddress = @EmailAddress
            WHERE ES.ID IS NULL 

        IF ISNULL(@DisabledEmailList,'') > ''    
        Update ES
        SET HasOpted = 0,
            updatedate = @currentdate
        FROM
            EmailList_Subscription ES
            INNER JOIN dbo.UFN_PARSE_STRING (@DisabledEmailList,',') EL
            ON EL.element_value = ES.EmailListID AND ES.EmailAddress = @EmailAddress

    END
END