USP_BBNC_CONSTITPRIMARYADDRESSDATA

            CREATE procedure dbo.USP_BBNC_CONSTITPRIMARYADDRESSDATA(@ID int)
            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
                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
                where
                    CONSTITUENT.SEQUENCEID = @ID and ADDRESS.ISPRIMARY = 1;