USP_ADDRESS_UPDATE
Updates an existing address.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @ADDRESSTYPECODEID | uniqueidentifier | IN | |
| @PRIMARY | bit | IN | |
| @DONOTMAIL | bit | IN | |
| @STARTDATE | UDT_MONTHDAY | IN | |
| @ENDDATE | UDT_MONTHDAY | IN | |
| @COUNTRYID | uniqueidentifier | IN | |
| @STATEID | uniqueidentifier | IN | |
| @ADDRESSBLOCK | nvarchar(150) | IN | |
| @CITY | nvarchar(50) | IN | |
| @POSTCODE | nvarchar(12) | IN | |
| @CART | nvarchar(10) | IN | |
| @DPC | nvarchar(8) | IN | |
| @LOT | nvarchar(5) | IN | |
| @UPDATEMATCHINGSPOUSEADDRESSES | bit | IN | |
| @OMITFROMVALIDATION | bit | IN | |
| @COUNTYCODEID | uniqueidentifier | IN | |
| @CONGRESSIONALDISTRICTCODEID | uniqueidentifier | IN | |
| @STATEHOUSEDISTRICTCODEID | uniqueidentifier | IN | |
| @STATESENATEDISTRICTCODEID | uniqueidentifier | IN | |
| @LOCALPRECINCTCODEID | uniqueidentifier | IN | |
| @INFOSOURCECODEID | uniqueidentifier | IN | |
| @REGIONCODEID | uniqueidentifier | IN | |
| @LASTVALIDATIONATTEMPTDATE | datetime | IN | |
| @VALIDATIONMESSAGE | nvarchar(200) | IN | |
| @CERTIFICATIONDATA | int | IN | |
| @UPDATECONTACTS | bit | IN | |
| @DONOTMAILREASONCODEID | uniqueidentifier | IN | |
| @HISTORICALSTARTDATE | date | IN | |
| @HISTORICALENDDATE | date | IN | |
| @INFOSOURCECOMMENTS | nvarchar(256) | IN | |
| @ISCONFIDENTIAL | bit | IN |
Definition
Copy
CREATE procedure dbo.USP_ADDRESS_UPDATE
(
@ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@ADDRESSTYPECODEID uniqueidentifier,
@PRIMARY bit,
@DONOTMAIL bit,
@STARTDATE dbo.UDT_MONTHDAY,
@ENDDATE dbo.UDT_MONTHDAY,
@COUNTRYID uniqueidentifier,
@STATEID uniqueidentifier,
@ADDRESSBLOCK nvarchar(150),
@CITY nvarchar(50),
@POSTCODE nvarchar(12),
@CART nvarchar(10),
@DPC nvarchar(8),
@LOT nvarchar(5),
@UPDATEMATCHINGSPOUSEADDRESSES bit,
@OMITFROMVALIDATION bit,
@COUNTYCODEID uniqueidentifier,
@CONGRESSIONALDISTRICTCODEID uniqueidentifier,
@STATEHOUSEDISTRICTCODEID uniqueidentifier,
@STATESENATEDISTRICTCODEID uniqueidentifier,
@LOCALPRECINCTCODEID uniqueidentifier,
@INFOSOURCECODEID uniqueidentifier,
@REGIONCODEID uniqueidentifier,
@LASTVALIDATIONATTEMPTDATE datetime,
@VALIDATIONMESSAGE nvarchar(200),
@CERTIFICATIONDATA integer,
@UPDATECONTACTS bit,
@DONOTMAILREASONCODEID uniqueidentifier = null,
@HISTORICALSTARTDATE date = null,
@HISTORICALENDDATE date = null,
@INFOSOURCECOMMENTS nvarchar(256) = '',
@ISCONFIDENTIAL bit = 0
)
as
set nocount on;
declare @CURRENTDATE datetime;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
if @DONOTMAIL = 0
set @DONOTMAILREASONCODEID = null
set @CURRENTDATE = getdate();
declare @CONSTITUENTID uniqueidentifier;
declare @OLDCOUNTRYID uniqueidentifier;
declare @OLDSTATEID uniqueidentifier;
declare @OLDADDRESSBLOCK nvarchar(150);
declare @OLDCITY nvarchar(50);
declare @OLDPOSTCODE nvarchar(12);
select
@CONSTITUENTID = ADDRESS.[CONSTITUENTID],
@OLDCOUNTRYID = ADDRESS.[COUNTRYID],
@OLDADDRESSBLOCK = ADDRESS.[ADDRESSBLOCK],
@OLDCITY = ADDRESS.[CITY],
@OLDSTATEID = ADDRESS.[STATEID],
@OLDPOSTCODE = ADDRESS.[POSTCODE]
from
dbo.ADDRESS
where
ADDRESS.[ID] = @ID;
if @CERTIFICATIONDATA is null
set @CERTIFICATIONDATA = 0;
begin try
if @PRIMARY = 1
update
dbo.[ADDRESS]
set
ISPRIMARY = 0,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
where
CONSTITUENTID = @CONSTITUENTID and ISPRIMARY = 1 and ID <> @ID;
update
dbo.ADDRESS
set
ADDRESSTYPECODEID = @ADDRESSTYPECODEID,
ISPRIMARY = @PRIMARY,
DONOTMAIL = @DONOTMAIL,
STARTDATE = @STARTDATE,
ENDDATE = @ENDDATE,
COUNTRYID = @COUNTRYID,
STATEID = @STATEID,
ADDRESSBLOCK = @ADDRESSBLOCK,
CITY = @CITY,
POSTCODE = @POSTCODE,
CART = @CART,
DPC = @DPC,
LOT = @LOT,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE,
DONOTMAILREASONCODEID = @DONOTMAILREASONCODEID,
HISTORICALSTARTDATE = @HISTORICALSTARTDATE,
HISTORICALENDDATE = @HISTORICALENDDATE,
ISCONFIDENTIAL = @ISCONFIDENTIAL
where
ID = @ID;
if exists (select ID from dbo.ADDRESSVALIDATIONUPDATE where ID = @ID)
update dbo.ADDRESSVALIDATIONUPDATE
set OMITFROMVALIDATION = @OMITFROMVALIDATION,
COUNTYCODEID = @COUNTYCODEID,
CONGRESSIONALDISTRICTCODEID = @CONGRESSIONALDISTRICTCODEID,
STATEHOUSEDISTRICTCODEID = @STATEHOUSEDISTRICTCODEID,
STATESENATEDISTRICTCODEID = @STATESENATEDISTRICTCODEID,
LOCALPRECINCTCODEID = @LOCALPRECINCTCODEID,
INFOSOURCECODEID = @INFOSOURCECODEID,
INFOSOURCECOMMENTS = @INFOSOURCECOMMENTS,
REGIONCODEID = @REGIONCODEID,
LASTVALIDATIONATTEMPTDATE = @LASTVALIDATIONATTEMPTDATE,
VALIDATIONMESSAGE = @VALIDATIONMESSAGE,
CERTIFICATIONDATA = @CERTIFICATIONDATA,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @ID;
else
insert into dbo.ADDRESSVALIDATIONUPDATE
(ID, OMITFROMVALIDATION, COUNTYCODEID, CONGRESSIONALDISTRICTCODEID, STATEHOUSEDISTRICTCODEID, STATESENATEDISTRICTCODEID, LOCALPRECINCTCODEID, INFOSOURCECODEID, INFOSOURCECOMMENTS, REGIONCODEID, LASTVALIDATIONATTEMPTDATE, VALIDATIONMESSAGE, CERTIFICATIONDATA, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values
(@ID, @OMITFROMVALIDATION, @COUNTYCODEID, @CONGRESSIONALDISTRICTCODEID, @STATEHOUSEDISTRICTCODEID, @STATESENATEDISTRICTCODEID, @LOCALPRECINCTCODEID, @INFOSOURCECODEID, @INFOSOURCECOMMENTS, @REGIONCODEID, @LASTVALIDATIONATTEMPTDATE, @VALIDATIONMESSAGE, coalesce(@CERTIFICATIONDATA, 0), @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
if @UPDATEMATCHINGSPOUSEADDRESSES = 1 and exists(select object_id from sys.objects where type = 'U' and name = 'RELATIONSHIP') begin
declare @SPOUSEID uniqueidentifier;
declare @EXISTINGID uniqueidentifier;
select @SPOUSEID = RECIPROCALCONSTITUENTID from dbo.RELATIONSHIP where RELATIONSHIPCONSTITUENTID = @CONSTITUENTID and ISSPOUSE = 1;
if @SPOUSEID is not null
begin
if @PRIMARY = 1
update dbo.ADDRESS
set
ISPRIMARY = 0,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
CONSTITUENTID = @SPOUSEID and
ISPRIMARY = 1
select
@EXISTINGID = ID
from
dbo.ADDRESS
where
CONSTITUENTID = @SPOUSEID and
COUNTRYID = @OLDCOUNTRYID and
ADDRESSBLOCK = @OLDADDRESSBLOCK and
CITY = @OLDCITY and
(STATEID = @OLDSTATEID or (STATEID is null and @OLDSTATEID is null)) and
POSTCODE = @OLDPOSTCODE
if @EXISTINGID is null
begin
declare @NEWID uniqueidentifier;
set @NEWID = newID();
insert into dbo.ADDRESS
(
ID,
CONSTITUENTID,
ADDRESSTYPECODEID,
DONOTMAIL,
STARTDATE,
ENDDATE,
COUNTRYID,
STATEID,
ADDRESSBLOCK,
CITY,
POSTCODE,
CART,
DPC,
LOT,
ISPRIMARY,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED,
DONOTMAILREASONCODEID,
HISTORICALSTARTDATE,
HISTORICALENDDATE,
ISCONFIDENTIAL
)
values
(
@NEWID,
@SPOUSEID,
@ADDRESSTYPECODEID,
@DONOTMAIL,
@STARTDATE,
@ENDDATE,
@COUNTRYID,
@STATEID,
@ADDRESSBLOCK,
@CITY,
@POSTCODE,
@CART,
@DPC,
@LOT,
@PRIMARY,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
@DONOTMAILREASONCODEID,
@HISTORICALSTARTDATE,
@HISTORICALENDDATE,
@ISCONFIDENTIAL
)
insert into dbo.ADDRESSVALIDATIONUPDATE
(
ID,
OMITFROMVALIDATION,
COUNTYCODEID,
CONGRESSIONALDISTRICTCODEID,
STATEHOUSEDISTRICTCODEID,
STATESENATEDISTRICTCODEID,
LOCALPRECINCTCODEID,
INFOSOURCECODEID,
INFOSOURCECOMMENTS,
REGIONCODEID,
LASTVALIDATIONATTEMPTDATE,
VALIDATIONMESSAGE,
CERTIFICATIONDATA,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@NEWID,
@OMITFROMVALIDATION,
@COUNTYCODEID,
@CONGRESSIONALDISTRICTCODEID,
@STATEHOUSEDISTRICTCODEID,
@STATESENATEDISTRICTCODEID,
@LOCALPRECINCTCODEID,
@INFOSOURCECODEID,
@INFOSOURCECOMMENTS,
@REGIONCODEID,
@LASTVALIDATIONATTEMPTDATE,
@VALIDATIONMESSAGE,
@CERTIFICATIONDATA,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
)
end
else
begin
update dbo.ADDRESS
set
ADDRESSTYPECODEID = @ADDRESSTYPECODEID,
DONOTMAIL = @DONOTMAIL,
STARTDATE = @STARTDATE,
ENDDATE = @ENDDATE,
COUNTRYID = @COUNTRYID,
STATEID = @STATEID,
ADDRESSBLOCK = @ADDRESSBLOCK,
CITY = @CITY,
POSTCODE = @POSTCODE,
CART = @CART,
DPC = @DPC,
LOT = @LOT,
ISPRIMARY = @PRIMARY,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE,
DONOTMAILREASONCODEID = @DONOTMAILREASONCODEID,
HISTORICALSTARTDATE = @HISTORICALSTARTDATE,
HISTORICALENDDATE = @HISTORICALENDDATE,
ISCONFIDENTIAL = @ISCONFIDENTIAL
where
ID = @EXISTINGID
if exists (select ID from dbo.ADDRESSVALIDATIONUPDATE where ID = @ID)
update dbo.ADDRESSVALIDATIONUPDATE
set OMITFROMVALIDATION = @OMITFROMVALIDATION,
COUNTYCODEID = @COUNTYCODEID,
CONGRESSIONALDISTRICTCODEID = @CONGRESSIONALDISTRICTCODEID,
STATEHOUSEDISTRICTCODEID = @STATEHOUSEDISTRICTCODEID,
STATESENATEDISTRICTCODEID = @STATESENATEDISTRICTCODEID,
LOCALPRECINCTCODEID = @LOCALPRECINCTCODEID,
INFOSOURCECODEID = @INFOSOURCECODEID,
INFOSOURCECOMMENTS = @INFOSOURCECOMMENTS,
REGIONCODEID = @REGIONCODEID,
LASTVALIDATIONATTEMPTDATE = @LASTVALIDATIONATTEMPTDATE,
VALIDATIONMESSAGE = @VALIDATIONMESSAGE,
CERTIFICATIONDATA = @CERTIFICATIONDATA,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @EXISTINGID;
else
insert into dbo.ADDRESSVALIDATIONUPDATE
(ID, OMITFROMVALIDATION, COUNTYCODEID, CONGRESSIONALDISTRICTCODEID, STATEHOUSEDISTRICTCODEID, STATESENATEDISTRICTCODEID, LOCALPRECINCTCODEID, INFOSOURCECODEID, INFOSOURCECOMMENTS, REGIONCODEID, LASTVALIDATIONATTEMPTDATE, VALIDATIONMESSAGE, CERTIFICATIONDATA, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values
(@EXISTINGID, @OMITFROMVALIDATION, @COUNTYCODEID, @CONGRESSIONALDISTRICTCODEID, @STATEHOUSEDISTRICTCODEID, @STATESENATEDISTRICTCODEID, @LOCALPRECINCTCODEID, @INFOSOURCECODEID, @INFOSOURCECOMMENTS, @REGIONCODEID, @LASTVALIDATIONATTEMPTDATE, @VALIDATIONMESSAGE, coalesce(@CERTIFICATIONDATA, 0), @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
end
end
end
if @UPDATECONTACTS = 1
begin
update
dbo.ADDRESS
set
ADDRESSTYPECODEID = @ADDRESSTYPECODEID,
--ISPRIMARY = @PRIMARY,
--DONOTMAIL = @DONOTMAIL,
STARTDATE = @STARTDATE,
ENDDATE = @ENDDATE,
COUNTRYID = @COUNTRYID,
STATEID = @STATEID,
ADDRESSBLOCK = @ADDRESSBLOCK,
CITY = @CITY,
POSTCODE = @POSTCODE,
CART = @CART,
DPC = @DPC,
LOT = @LOT,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID,
HISTORICALSTARTDATE = @HISTORICALSTARTDATE,
HISTORICALENDDATE = @HISTORICALENDDATE,
ISCONFIDENTIAL = @ISCONFIDENTIAL
from dbo.ADDRESS a
inner join dbo.RELATIONSHIP r on r.ID=a.RELATIONSHIPID
where
r.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID and
r.ISCONTACT=1 and
a.COUNTRYID = @OLDCOUNTRYID and
(a.STATEID = @OLDSTATEID or (a.STATEID is null and @OLDSTATEID is null)) and
(a.ADDRESSBLOCK = @OLDADDRESSBLOCK or (a.ADDRESSBLOCK is null and @OLDADDRESSBLOCK is null)) and
(a.CITY = @OLDCITY or (a.CITY is null and @OLDCITY is null)) and
(a.POSTCODE = @OLDPOSTCODE or (a.POSTCODE is null and @OLDPOSTCODE is null));
update dbo.ADDRESSVALIDATIONUPDATE
set --OMITFROMVALIDATION = @OMITFROMVALIDATION,
COUNTYCODEID = @COUNTYCODEID,
CONGRESSIONALDISTRICTCODEID = @CONGRESSIONALDISTRICTCODEID,
STATEHOUSEDISTRICTCODEID = @STATEHOUSEDISTRICTCODEID,
STATESENATEDISTRICTCODEID = @STATESENATEDISTRICTCODEID,
LOCALPRECINCTCODEID = @LOCALPRECINCTCODEID,
INFOSOURCECODEID = @INFOSOURCECODEID,
INFOSOURCECOMMENTS = @INFOSOURCECOMMENTS,
REGIONCODEID = @REGIONCODEID,
--LASTVALIDATIONATTEMPTDATE = @LASTVALIDATIONATTEMPTDATE,
--VALIDATIONMESSAGE = @VALIDATIONMESSAGE,
--CERTIFICATIONDATA = @CERTIFICATIONDATA,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
where ID in (
select a.ID
from dbo.ADDRESS a
inner join dbo.RELATIONSHIP r on r.ID=a.RELATIONSHIPID
where
r.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID and
r.ISCONTACT=1 and
a.COUNTRYID = @OLDCOUNTRYID and
(a.STATEID = @OLDSTATEID or (a.STATEID is null and @OLDSTATEID is null)) and
(a.ADDRESSBLOCK = @OLDADDRESSBLOCK or (a.ADDRESSBLOCK is null and @OLDADDRESSBLOCK is null)) and
(a.CITY = @OLDCITY or (a.CITY is null and @OLDCITY is null)) and
(a.POSTCODE = @OLDPOSTCODE or (a.POSTCODE is null and @OLDPOSTCODE is null))
);
insert into dbo.ADDRESSVALIDATIONUPDATE
(ID, OMITFROMVALIDATION, COUNTYCODEID, CONGRESSIONALDISTRICTCODEID, STATEHOUSEDISTRICTCODEID, STATESENATEDISTRICTCODEID, LOCALPRECINCTCODEID, INFOSOURCECODEID, INFOSOURCECOMMENTS, REGIONCODEID, LASTVALIDATIONATTEMPTDATE, VALIDATIONMESSAGE, CERTIFICATIONDATA, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
(select ID, @OMITFROMVALIDATION, @COUNTYCODEID, @CONGRESSIONALDISTRICTCODEID, @STATEHOUSEDISTRICTCODEID, @STATESENATEDISTRICTCODEID, @LOCALPRECINCTCODEID, @INFOSOURCECODEID, @INFOSOURCECOMMENTS, @REGIONCODEID, @LASTVALIDATIONATTEMPTDATE, @VALIDATIONMESSAGE, coalesce(@CERTIFICATIONDATA, 0), @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from dbo.ADDRESS where ID in (
select a.ID
from dbo.ADDRESS a
inner join dbo.RELATIONSHIP r on r.ID=a.RELATIONSHIPID
where
r.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID and
r.ISCONTACT=1 and
a.COUNTRYID = @OLDCOUNTRYID and
(a.STATEID = @OLDSTATEID or (a.STATEID is null and @OLDSTATEID is null)) and
(a.ADDRESSBLOCK = @OLDADDRESSBLOCK or (a.ADDRESSBLOCK is null and @OLDADDRESSBLOCK is null)) and
(a.CITY = @OLDCITY or (a.CITY is null and @OLDCITY is null)) and
(a.POSTCODE = @OLDPOSTCODE or (a.POSTCODE is null and @OLDPOSTCODE is null))
) and ID not in (select ID from dbo.ADDRESSVALIDATIONUPDATE));
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;