USP_DATAFORMTEMPLATE_LOAD_ADDRESSBATCHADDRESS

The load procedure used by the view dataform template "Address Batch Address View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@CONSTITUENTNAME	nvarchar(154)	INOUT	Constituent name
@ADDRESSTYPE	nvarchar(100)	INOUT	Address type
@PRIMARY	bit	INOUT	Set as primary address
@DONOTMAIL	bit	INOUT	Do not send mail to this address
@STARTDATE	UDT_MONTHDAY	INOUT	Start date
@ENDDATE	UDT_MONTHDAY	INOUT	End date
@COUNTRY	nvarchar(100)	INOUT	Country
@STATE	nvarchar(100)	INOUT	State
@ADDRESSBLOCK	nvarchar(150)	INOUT	Address
@CITY	nvarchar(50)	INOUT	City
@POSTCODE	nvarchar(12)	INOUT	Zip/Postal code
@CART	nvarchar(10)	INOUT	CART
@DPC	nvarchar(8)	INOUT	DPC
@LOT	nvarchar(5)	INOUT	LOT
@COUNTY	nvarchar(100)	INOUT	County
@CONGRESSIONALDISTRICT	nvarchar(100)	INOUT	Congressional district
@STATEHOUSEDISTRICT	nvarchar(100)	INOUT	State house district
@STATESENATEDISTRICT	nvarchar(100)	INOUT	State senate district
@LOCALPRECINCT	nvarchar(100)	INOUT	Local precinct
@INFOSOURCE	nvarchar(100)	INOUT	Information source
@REGION	nvarchar(100)	INOUT	Region
@DONOTMAILREASON	nvarchar(100)	INOUT	Reason

Definition

Copy



                CREATE procedure dbo.USP_DATAFORMTEMPLATE_LOAD_ADDRESSBATCHADDRESS
                (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @CONSTITUENTNAME nvarchar(154) = null output,
                    @ADDRESSTYPE nvarchar(100) = null output,
                    @PRIMARY bit = null output,
                    @DONOTMAIL bit = null output,
                    @STARTDATE dbo.UDT_MONTHDAY = null output,
                    @ENDDATE dbo.UDT_MONTHDAY = null output,
                    @COUNTRY nvarchar(100) = null output,
                    @STATE nvarchar(100) = null output,
                    @ADDRESSBLOCK nvarchar(150) = null output,
                    @CITY nvarchar(50) = null output,
                    @POSTCODE nvarchar(12) = null output,
                    @CART nvarchar(10) = null output,
                    @DPC nvarchar(8) = null output,
                    @LOT nvarchar(5) = null output,
                    @COUNTY nvarchar(100) = null output,
                    @CONGRESSIONALDISTRICT nvarchar(100) = null output,
                    @STATEHOUSEDISTRICT nvarchar(100) = null output,
                    @STATESENATEDISTRICT nvarchar(100) = null output,
                    @LOCALPRECINCT nvarchar(100) = null output,
                    @INFOSOURCE nvarchar(100) = null output,
                    @REGION nvarchar(100) = null output,                    
                    @DONOTMAILREASON nvarchar(100) = null output
                )
                as
                    set nocount on

                    select
                        @DATALOADED = 1,
                        @CONSTITUENTNAME = CONSTITUENT.NAME,
                        @ADDRESSTYPE = ADDRESSTYPECODE.DESCRIPTION,
                        @PRIMARY = ADDRESS.ISPRIMARY,
                        @DONOTMAIL = ADDRESS.DONOTMAIL,
                        @STARTDATE = ADDRESS.STARTDATE,
                        @ENDDATE = ADDRESS.ENDDATE,
                        @COUNTRY = COUNTRY.DESCRIPTION,
                        @STATE = STATE.DESCRIPTION,
                        @ADDRESSBLOCK = ADDRESS.ADDRESSBLOCK,
                        @CITY = ADDRESS.CITY,
                        @POSTCODE = ADDRESS.POSTCODE,
                        @CART = ADDRESS.CART,
                        @DPC = ADDRESS.DPC,
                        @LOT = ADDRESS.LOT,
                        @COUNTY = COUNTYCODE.DESCRIPTION,
                        @CONGRESSIONALDISTRICT = CONGRESSIONALDISTRICTCODE.DESCRIPTION,
                        @STATEHOUSEDISTRICT = STATEHOUSEDISTRICTCODE.DESCRIPTION,
                        @STATESENATEDISTRICT = STATESENATEDISTRICTCODE.DESCRIPTION,
                        @LOCALPRECINCT = LOCALPRECINCTCODE.DESCRIPTION,
                        @INFOSOURCE = INFOSOURCECODE.DESCRIPTION,
                        @REGION = REGIONCODE.DESCRIPTION,                                
                        @DONOTMAILREASON = DONOTMAILREASONCODE.DESCRIPTION
                    from dbo.ADDRESS
                    inner join dbo.CONSTITUENT on ADDRESS.CONSTITUENTID = CONSTITUENT.ID
                    inner join dbo.COUNTRY on ADDRESS.COUNTRYID = COUNTRY.ID
                    left join dbo.ADDRESSVALIDATIONUPDATE on ADDRESS.ID = ADDRESSVALIDATIONUPDATE.ID
                    left join dbo.ADDRESSTYPECODE on ADDRESS.ADDRESSTYPECODEID = ADDRESSTYPECODE.ID
                    left join dbo.STATE on ADDRESS.STATEID = STATE.ID
                    left join dbo.COUNTYCODE on ADDRESSVALIDATIONUPDATE.COUNTYCODEID = COUNTYCODE.ID
                    left join dbo.DONOTMAILREASONCODE on ADDRESS.DONOTMAILREASONCODEID = DONOTMAILREASONCODE.ID
                    left join dbo.CONGRESSIONALDISTRICTCODE on ADDRESSVALIDATIONUPDATE.CONGRESSIONALDISTRICTCODEID = CONGRESSIONALDISTRICTCODE.ID
                    left join dbo.STATEHOUSEDISTRICTCODE on ADDRESSVALIDATIONUPDATE.STATEHOUSEDISTRICTCODEID = STATEHOUSEDISTRICTCODE.ID
                    left join dbo.STATESENATEDISTRICTCODE on ADDRESSVALIDATIONUPDATE.STATESENATEDISTRICTCODEID = STATESENATEDISTRICTCODE.ID
                    left join dbo.LOCALPRECINCTCODE on ADDRESSVALIDATIONUPDATE.LOCALPRECINCTCODEID = LOCALPRECINCTCODE.ID
                    left join dbo.INFOSOURCECODE on ADDRESSVALIDATIONUPDATE.INFOSOURCECODEID = INFOSOURCECODE.ID
                    left join dbo.REGIONCODE on ADDRESSVALIDATIONUPDATE.REGIONCODEID = REGIONCODE.ID
                    where
                        ADDRESS.ID = @ID;