USP_BBNC_INTEGRATIONEMAILINFOADDUPDATE

            CREATE procedure dbo.USP_BBNC_INTEGRATIONEMAILINFOADDUPDATE
            (
                @EMAILINFOMAPID int,
                @BBNCUSERID int,
                @OPTEDIN bit,
                @FORWARDINGENABLED bit,
                @INCOMINGEMAILADDRESS nvarchar(510),
                @FORWARDINGEMAILADDRESS nvarchar(510),
                @UPDATEDATE datetime,
                @CHANGEAGENTID uniqueidentifier = null,
                @CHANGEDATE datetime = null
            )
            as
                set nocount on;

                if @CHANGEAGENTID is null
                    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

                if @CHANGEDATE is null
                    set @CHANGEDATE = getdate();

                begin try
                    if not (@EMAILINFOMAPID > 0)
                        raiserror('ERR_EMAILINFOMAPID_REQUIRED',16,1);

                    declare @NETCOMMUNITYCLIENTUSERID uniqueidentifier;
                    if @BBNCUSERID > 0
                        begin
                            select
                                @NETCOMMUNITYCLIENTUSERID = NETCOMMUNITYCLIENTUSER.ID
                            from
                                dbo.NETCOMMUNITYCLIENTUSER
                            where
                                BBNCUSERID = @BBNCUSERID;

                            if @NETCOMMUNITYCLIENTUSERID is null
                                begin
                                    set @NETCOMMUNITYCLIENTUSERID = newid();
                                    insert into dbo.NETCOMMUNITYCLIENTUSER
                                    (
                                        [ID],
                                        [BBNCUSERID],
                                        [ADDEDBYID],
                                        [CHANGEDBYID],
                                        [DATEADDED],
                                        [DATECHANGED]
                                    )
                                    values
                                    (
                                        @NETCOMMUNITYCLIENTUSERID,
                                        @BBNCUSERID,
                                        @CHANGEAGENTID,
                                        @CHANGEAGENTID,
                                        @CHANGEDATE,
                                        @CHANGEDATE
                                    );
                                end
                        end

                    if exists
                    (
                        select
                            [ID]
                        from
                            dbo.NETCOMMUNITYEMAILINFO
                        where
                            @EMAILINFOMAPID = [BBNCMAPID]
                    )
                        update dbo.NETCOMMUNITYEMAILINFO
                        set
                            [NETCOMMUNITYCLIENTUSERID] = @NETCOMMUNITYCLIENTUSERID,
                            [OPTEDIN] = coalesce(@OPTEDIN, 0),
                            [FORWARDINGENABLED] = coalesce(@FORWARDINGENABLED, 0),
                            [INCOMINGEMAILADDRESS] = coalesce(@INCOMINGEMAILADDRESS, ''),
                            [FORWARDINGEMAILADDRESS] = coalesce(@FORWARDINGEMAILADDRESS, ''),
                            [UPDATEDATE] = @UPDATEDATE,
                            [CHANGEDBYID] = @CHANGEAGENTID,
                            [DATECHANGED] = @CHANGEDATE
                        where
                            [BBNCMAPID] = @EMAILINFOMAPID
                            and
                            (
                                coalesce([NETCOMMUNITYCLIENTUSERID], '00000000-0000-0000-0000-000000000000') <> coalesce(@NETCOMMUNITYCLIENTUSERID, '00000000-0000-0000-0000-000000000000')
                                or [OPTEDIN] <> coalesce(@OPTEDIN, 0)
                                or [FORWARDINGENABLED] <> coalesce(@FORWARDINGENABLED, 0)
                                or [INCOMINGEMAILADDRESS] <> coalesce(@INCOMINGEMAILADDRESS, '')
                                or [FORWARDINGEMAILADDRESS] <> coalesce(@FORWARDINGEMAILADDRESS, '')
                                or ([UPDATEDATE] <> @UPDATEDATE or ([UPDATEDATE] is null and @UPDATEDATE is not null) or ([UPDATEDATE] is not null and @UPDATEDATE is null))
                            );
                    else
                        insert into dbo.NETCOMMUNITYEMAILINFO
                        (
                            [BBNCMAPID],
                            [NETCOMMUNITYCLIENTUSERID],
                            [OPTEDIN],
                            [FORWARDINGENABLED],
                            [INCOMINGEMAILADDRESS],
                            [FORWARDINGEMAILADDRESS],
                            [UPDATEDATE],
                            [ADDEDBYID],
                            [CHANGEDBYID],
                            [DATEADDED],
                            [DATECHANGED]
                        )
                        values
                        (
                            @EMAILINFOMAPID,
                            @NETCOMMUNITYCLIENTUSERID,
                            coalesce(@OPTEDIN, 0),
                            coalesce(@FORWARDINGENABLED, 0),
                            coalesce(@INCOMINGEMAILADDRESS, ''),
                            coalesce(@FORWARDINGEMAILADDRESS, ''),
                            @UPDATEDATE,
                            @CHANGEAGENTID,
                            @CHANGEAGENTID,
                            @CHANGEDATE,
                            @CHANGEDATE
                        );
                end try
                begin catch
                    exec dbo.USP_RAISE_ERROR;
                    return 1;
                end catch

                return 0;