USP_EMAILADDRESS_UPDATE

Updates an existing email address.
Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN
@CURRENTDATE	datetime	IN
@EMAILADDRESSTYPECODEID	uniqueidentifier	IN
@EMAILADDRESS	UDT_EMAILADDRESS	IN
@PRIMARY	bit	IN
@DONOTEMAIL	bit	IN
@UPDATEMATCHINGHOUSEHOLDEMAILADDRESS	bit	IN
@INFOSOURCECODEID	uniqueidentifier	IN
@INFOSOURCECOMMENTS	nvarchar(256)	IN
@STARTDATE	date	IN
@ENDDATE	date	IN
@EMAILISCONFIDENTIAL	bit	IN
@DONOTEMAILREASONCODEID	uniqueidentifier	IN
Definition

Copy


            CREATE procedure dbo.USP_EMAILADDRESS_UPDATE
            (
                @ID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier = null,
                @CURRENTDATE datetime = null,
                @EMAILADDRESSTYPECODEID uniqueidentifier,
                @EMAILADDRESS dbo.UDT_EMAILADDRESS,
                @PRIMARY bit,
                @DONOTEMAIL bit,
                @UPDATEMATCHINGHOUSEHOLDEMAILADDRESS bit,
                @INFOSOURCECODEID uniqueidentifier,
                @INFOSOURCECOMMENTS nvarchar(256),
                @STARTDATE date = null,
                @ENDDATE date = null,
                @EMAILISCONFIDENTIAL bit=0,
                @DONOTEMAILREASONCODEID uniqueidentifier = null
            )
            as
                set nocount on;

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

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

                declare @CONSTITUENTID uniqueidentifier;
                declare @OLDEMAILADDRESS dbo.UDT_EMAILADDRESS;
                declare @OLDEMAILADDRESSTYPECODEID uniqueidentifier;

                if @DONOTEMAIL = 0
                begin
                    set @DONOTEMAILREASONCODEID = null
                end

                select
                    @CONSTITUENTID = EMAILADDRESS.[CONSTITUENTID],
                    @OLDEMAILADDRESS = EMAILADDRESS.[EMAILADDRESS],
                    @OLDEMAILADDRESSTYPECODEID = EMAILADDRESS.[EMAILADDRESSTYPECODEID]
                from
                    dbo.[EMAILADDRESS]
                where
                    EMAILADDRESS.[ID] = @ID;

                begin try
                    if @PRIMARY = 1
                        update
                            dbo.EMAILADDRESS
                        set
                            ISPRIMARY = 0,
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CURRENTDATE
                        where
                            CONSTITUENTID = @CONSTITUENTID and
                            ISPRIMARY = 1 and
                            ID <> @ID;

                    update
                        dbo.EMAILADDRESS
                    set
                        EMAILADDRESSTYPECODEID = @EMAILADDRESSTYPECODEID,
                        EMAILADDRESS = @EMAILADDRESS,
                        ISPRIMARY = @PRIMARY,
                        DONOTEMAIL = @DONOTEMAIL,
                        INFOSOURCECODEID = @INFOSOURCECODEID,
                        INFOSOURCECOMMENTS = @INFOSOURCECOMMENTS,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE,
                        STARTDATE = @STARTDATE,
                        ENDDATE = @ENDDATE,
                        ISCONFIDENTIAL = @EMAILISCONFIDENTIAL,
                        DONOTEMAILREASONCODEID = @DONOTEMAILREASONCODEID
                    where
                        ID = @ID;

                    if @UPDATEMATCHINGHOUSEHOLDEMAILADDRESS = 1 begin
                        declare @MATCHINGCONSTITUENTS table(ID uniqueidentifier);
                        insert into @MATCHINGCONSTITUENTS select CONSTITUENTID from dbo.UFN_EMAILADDRESS_MATCHINGHOUSEHOLDRECORDS(@CONSTITUENTID, @OLDEMAILADDRESS, @OLDEMAILADDRESSTYPECODEID);

                        begin try
                            -- update the existing records

                            update dbo.EMAILADDRESS
                            set
                                EMAILADDRESSTYPECODEID = @EMAILADDRESSTYPECODEID,
                                EMAILADDRESS = @EMAILADDRESS,
                                DONOTEMAIL = @DONOTEMAIL,
                                INFOSOURCECODEID = @INFOSOURCECODEID,
                                INFOSOURCECOMMENTS = @INFOSOURCECOMMENTS,
                                CHANGEDBYID = @CHANGEAGENTID,
                                DATECHANGED = @CURRENTDATE,
                                STARTDATE = @STARTDATE,
                                ENDDATE = @ENDDATE,
                                ISCONFIDENTIAL = @EMAILISCONFIDENTIAL,
                                DONOTEMAILREASONCODEID = @DONOTEMAILREASONCODEID
                            where
                                EMAILADDRESS = @OLDEMAILADDRESS
                                and ( (EMAILADDRESSTYPECODEID = @OLDEMAILADDRESSTYPECODEID) or (EMAILADDRESSTYPECODEID is null and @OLDEMAILADDRESSTYPECODEID is null) )
                                and CONSTITUENTID in (select ID from @MATCHINGCONSTITUENTS);
                        end try
                        begin catch
                            if PATINDEX('%CK_EMAILADDRESS_ENDDATEINVALIDIFPRIMARY%', ERROR_MESSAGE()) > 0
                                raiserror('ERR_EMAILADDRESS_ENDDATEINVALIDIFPRIMARYFORANOTHERMEMBER', 13, 1);
                            else
                                exec dbo.USP_RAISE_ERROR;
                            return 1;
                        end catch
                    end
                end try
                begin catch
                    exec dbo.USP_RAISE_ERROR;
                    return 1;
                end catch

                return 0;