USP_BBNC_CONSTITADDRESSDATA

CREATE procedure dbo.USP_BBNC_CONSTITADDRESSDATA(@ID int,@PRIMARYADDRESS bit)
            as

                set NOCOUNT on;

                select
                    COUNTRY.DESCRIPTION as COUNTRYLONG,
                    COUNTRY.ABBREVIATION as COUNTRYSHORT,
                    ADDRESS.ADDRESSBLOCK,
                    ADDRESS.CITY,
                    case when ADDRESS.COUNTRYID in ('D81CEF85-7569-4B2E-8F2E-F7CF998A3342' --USA

                                                                                            ,'D9EE54CD-2183-490C-A3AD-11152B271335' --CANADA

                                                                                            ,'F189F24C-2538-46A1-8458-1E3F3967B843' --Aus 

                                                                                            )                        
                    then 
                        STATE.ABBREVIATION
                    else
                        STATE.DESCRIPTION
                    end as STATE,
                    ADDRESS.POSTCODE,
                    dbo.UFN_BUILDFULLADDRESS(ADDRESS.ID, ADDRESS.ADDRESSBLOCK, ADDRESS.CITY, ADDRESS.STATEID, ADDRESS.POSTCODE, ADDRESS.COUNTRYID) as FORMATTEDADDRESS,
                    COUNTRY.COUNTRYADDRESSFORMATID,
                    ADDRESS.ADDRESSTYPECODEID,
                    ADDRESS.ID,
                    ADDRESS.HISTORICALSTARTDATE,
                    ADDRESS.HISTORICALENDDATE,
                    ADDRESS.STARTDATE,
                    ADDRESS.ENDDATE,
                    ADDRESS.DONOTMAIL,
                    ADDRESS.ISPRIMARY,
                    REGIONCODE.DESCRIPTION as REGION,
                    STATE.ABBREVIATION STATEISO
                from
                    dbo.CONSTITUENT
                inner join
                    dbo.ADDRESS on CONSTITUENT.ID = ADDRESS.CONSTITUENTID
                left join
                    dbo.COUNTRY on ADDRESS.COUNTRYID = COUNTRY.ID
                left join
                    dbo.STATE on ADDRESS.STATEID = STATE.ID
                left join
                    dbo.ADDRESSVALIDATIONUPDATE on ADDRESSVALIDATIONUPDATE.ID = ADDRESS.ID
                left join
                    dbo.REGIONCODE on REGIONCODE.ID = ADDRESSVALIDATIONUPDATE.REGIONCODEID
                where
                    CONSTITUENT.SEQUENCEID = @ID AND (@PRIMARYADDRESS = 0 OR  ADDRESS.ISPRIMARY = 1)
                    AND (ADDRESS.HISTORICALENDDATE is null OR ADDRESS.HISTORICALENDDATE > convert(date, GETDATE()))