UFN_CONSTITUENT_GETADDRESSES_FORUPDATEBATCH

            CREATE function dbo.UFN_CONSTITUENT_GETADDRESSES_FORUPDATEBATCH(@CONSTITUENTID uniqueidentifier)
            returns table
            as return
                select
                    A.ID as ADDRESSID,
                    coalesce(A.[ADDRESSBLOCK], '')as ADDRESSBLOCK,
                    coalesce(A.[ENDDATE], '0000') as ENDDATE,
                    coalesce(A.[STARTDATE], '0000') as STARTDATE,
                    A.[HISTORICALSTARTDATE],
                    A.[ADDRESSTYPECODEID],
                    coalesce(A.[CART], '') as CART,
                    coalesce(AVU.[CERTIFICATIONDATA], '0') as CERTIFICATIONDATA,
                    coalesce(A.[CITY], '') as CITY,
                    AVU.[CONGRESSIONALDISTRICTCODEID],
                    A.[COUNTRYID],
                    AVU.[COUNTYCODEID],
                    coalesce(A.[DONOTMAIL], cast(0 as bit)) as DONOTMAIL,
                    A.[DONOTMAILREASONCODEID],
                    coalesce(A.[DPC], '') as DPC,
                    AVU.[INFOSOURCECODEID],
                    coalesce(A.[ISPRIMARY], cast(0 as bit)) as ISPRIMARY,
                    AVU.[LASTVALIDATIONATTEMPTDATE],
                    AVU.[LOCALPRECINCTCODEID],
                    coalesce(A.[LOT], '') as LOT,
                    coalesce(AVU.[OMITFROMVALIDATION], cast(0 as bit)) as OMITFROMVALIDATION,
                    AVU.[REGIONCODEID],
                    A.[STATEID],
                    AVU.[STATEHOUSEDISTRICTCODEID],
                    AVU.[STATESENATEDISTRICTCODEID],
                    coalesce(AVU.[VALIDATIONMESSAGE], '') as VALIDATIONMESSAGE,
                    coalesce(A.[POSTCODE], '') as POSTCODE,
                    A.[SEQUENCE],
                    cast(1 as bit) as UPDATEHOUSEHOLD,
                    coalesce(AVU.INFOSOURCECOMMENTS, '') as INFOSOURCECOMMENTS,
                    A.HISTORICALENDDATE
                from
                    dbo.[ADDRESS] A
                left join
                    dbo.[ADDRESSVALIDATIONUPDATE] AVU on A.[ID] = AVU.[ID]
                where
                    A.[CONSTITUENTID] = @CONSTITUENTID;