USP_EMAILADDRESS_UPDATE_LOAD

Retrieves the information needed to update an email address.
Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@DATALOADED	bit	INOUT
@TSLONG	bigint	INOUT
@EMAILADDRESSTYPECODEID	uniqueidentifier	INOUT
@EMAILADDRESS	UDT_EMAILADDRESS	INOUT
@PRIMARY	bit	INOUT
@DONOTEMAIL	bit	INOUT
@SPOUSENAME	nvarchar(154)	INOUT
@SPOUSEHASMATCHINGEMAILADDRESS	bit	INOUT
@UPDATEMATCHINGSPOUSEEMAILADDRESS	bit	INOUT
@UPDATEMATCHINGHOUSEHOLDEMAILADDRESS	bit	INOUT
@INFOSOURCECODEID	uniqueidentifier	INOUT
@INFOSOURCECOMMENTS	nvarchar(256)	INOUT
@ORIGINCODE	tinyint	INOUT
@STARTDATE	date	INOUT
@ENDDATE	date	INOUT
@INVALIDEMAIL	bit	INOUT
@EMAILBOUNCEDDATE	date	INOUT
@EMAILISCONFIDENTIAL	bit	INOUT
@DONOTEMAILREASONCODEID	uniqueidentifier	INOUT
Definition

Copy


            CREATE procedure dbo.USP_EMAILADDRESS_UPDATE_LOAD
            (
                @ID uniqueidentifier,
                @DATALOADED bit = 0 output,
                @TSLONG bigint = 0 output,
                @EMAILADDRESSTYPECODEID uniqueidentifier = null output,
                @EMAILADDRESS dbo.UDT_EMAILADDRESS = null output,
                @PRIMARY bit = null output,
                @DONOTEMAIL bit = null output,
                @SPOUSENAME nvarchar(154) = null output,
                @SPOUSEHASMATCHINGEMAILADDRESS bit = null output,
                @UPDATEMATCHINGSPOUSEEMAILADDRESS bit = null output,
                @UPDATEMATCHINGHOUSEHOLDEMAILADDRESS bit = null output,
                @INFOSOURCECODEID uniqueidentifier = null output,
                @INFOSOURCECOMMENTS nvarchar(256) = null output,
                @ORIGINCODE tinyint = null output,
                @STARTDATE date = null output,
                @ENDDATE date = null output,
                @INVALIDEMAIL bit = null output,
                @EMAILBOUNCEDDATE date = null output,
                @EMAILISCONFIDENTIAL bit = null output,
                @DONOTEMAILREASONCODEID uniqueidentifier = null output
            )
            as
                set nocount on;

                declare @SPOUSEID uniqueidentifier;

                set @DATALOADED = 0;
                set @TSLONG = 0;
                set @SPOUSEHASMATCHINGEMAILADDRESS = 0;
                set @UPDATEMATCHINGSPOUSEEMAILADDRESS = 0;

                if exists(select object_id from sys.objects where type = 'U' and name = 'RELATIONSHIP') begin
                    select
                        @DATALOADED = 1,
                        @EMAILADDRESSTYPECODEID = EMAILADDRESS.EMAILADDRESSTYPECODEID,
                        @EMAILADDRESS = EMAILADDRESS.EMAILADDRESS,
                        @PRIMARY = EMAILADDRESS.ISPRIMARY,
                        @DONOTEMAIL = EMAILADDRESS.DONOTEMAIL,
                        @INFOSOURCECODEID = EMAILADDRESS.INFOSOURCECODEID,
                        @INFOSOURCECOMMENTS = EMAILADDRESS.INFOSOURCECOMMENTS,
                        @SPOUSEID = SPOUSE.ID,
                        @SPOUSENAME = SPOUSE.NAME,
                        @ORIGINCODE = EMAILADDRESS.ORIGINCODE,
                        @TSLONG = EMAILADDRESS.TSLONG,
                        @STARTDATE = EMAILADDRESS.STARTDATE,
                        @ENDDATE = EMAILADDRESS.ENDDATE,
                        @EMAILBOUNCEDDATE = (SELECT DATECHANGED FROM dbo.EMAILINVALIDRECIPIENT WHERE EMAILINVALIDRECIPIENT.ADDRESS = EMAILADDRESS.EMAILADDRESS AND EMAILINVALIDRECIPIENT.ISBLACKLISTED = 1),
                        @INVALIDEMAIL = case when (@EMAILBOUNCEDDATE is null) then 0 else 1 end,
                        @EMAILISCONFIDENTIAL = EMAILADDRESS.ISCONFIDENTIAL,
                        @DONOTEMAILREASONCODEID = EMAILADDRESS.DONOTEMAILREASONCODEID
                    from 
                        dbo.EMAILADDRESS
                        left join dbo.RELATIONSHIP on RELATIONSHIP.RELATIONSHIPCONSTITUENTID = EMAILADDRESS.CONSTITUENTID and RELATIONSHIP.ISSPOUSE = 1
                        left join dbo.CONSTITUENT as SPOUSE on SPOUSE.ID = RELATIONSHIP.RECIPROCALCONSTITUENTID
                    where 
                        EMAILADDRESS.ID = @ID;

                    if exists (
                        select EMAILADDRESS.ID
                        from dbo.EMAILADDRESS
                        where
                            EMAILADDRESS.CONSTITUENTID = @SPOUSEID and
                            EMAILADDRESS.EMAILADDRESS = @EMAILADDRESS
                        ) begin
                            set @SPOUSEHASMATCHINGEMAILADDRESS = 1;    
                            set @UPDATEMATCHINGSPOUSEEMAILADDRESS = 1;
                    end
                end
                else begin
                    select
                        @DATALOADED = 1,
                        @EMAILADDRESSTYPECODEID = EMAILADDRESS.EMAILADDRESSTYPECODEID,
                        @EMAILADDRESS = EMAILADDRESS.EMAILADDRESS,
                        @PRIMARY = EMAILADDRESS.ISPRIMARY,
                        @DONOTEMAIL = EMAILADDRESS.DONOTEMAIL,
                        @INFOSOURCECODEID = EMAILADDRESS.INFOSOURCECODEID,
                        @INFOSOURCECOMMENTS = EMAILADDRESS.INFOSOURCECOMMENTS,
                        @ORIGINCODE = EMAILADDRESS.ORIGINCODE,
                        @TSLONG = EMAILADDRESS.TSLONG,
                        @STARTDATE = EMAILADDRESS.STARTDATE,
                        @ENDDATE = EMAILADDRESS.ENDDATE,
                        @EMAILBOUNCEDDATE = (SELECT UPDATEDATE FROM dbo.EMAILINVALIDACCOUNT WHERE EMAILINVALIDACCOUNT.EMAILADDRESS = EMAILADDRESS.EMAILADDRESS AND EMAILINVALIDACCOUNT.ACTIVE = 1),
                        @INVALIDEMAIL = case when (@EMAILBOUNCEDDATE is null) then 0 else 1 end,
                        @EMAILISCONFIDENTIAL = EMAILADDRESS.ISCONFIDENTIAL,
                        @DONOTEMAILREASONCODEID = EMAILADDRESS.DONOTEMAILREASONCODEID
                    from
                        dbo.EMAILADDRESS
                    where
                        EMAILADDRESS.ID = @ID;
                end

                return 0;