USP_CONSTITUENT_GETDEFAULTADDRESS

            CREATE procedure dbo.USP_CONSTITUENT_GETDEFAULTADDRESS
            (
                @CONSTITUENTID uniqueidentifier = null
            )
            as
                set nocount on

                -- The CONSTITUENTID passed in could either be for a standard constituent or a revenue batch

                -- constituent.  If the row doesn't exist in the constituent table, then we assume it's a 

                -- revenue batch constituent.

                if exists (select 1 from dbo.CONSTITUENT where ID = @CONSTITUENTID)
                begin
                    select top 1 
                        ADDRESSBLOCK,
                        CITY,
                        STATE.ABBREVIATION as STATE,
                        POSTCODE,
                        COUNTRY.ISO3166 as COUNTRY,
                        (select EMAILADDRESS from dbo.EMAILADDRESS where CONSTITUENTID = @CONSTITUENTID and ISPRIMARY = 1) as EMAILADDRESS,
                        (select NUMBER from dbo.PHONE where CONSTITUENTID = @CONSTITUENTID and ISPRIMARY = 1) as PHONE
                    from dbo.ADDRESS
                    left join dbo.STATE on ADDRESS.STATEID = STATE.ID
                    inner join dbo.COUNTRY on ADDRESS.COUNTRYID = COUNTRY.ID
                    where 
                        ISPRIMARY = 1 and 
                        CONSTITUENTID = @CONSTITUENTID
                end
                else
                begin
                    select top 1
                        ADDRESSBLOCK,
                        CITY,
                        STATE.ABBREVIATION as STATE,
                        POSTCODE,
                        COUNTRY.ISO3166 as COUNTRY,
                        EMAILADDRESS,
                        NUMBER as PHONE
                    from dbo.BATCHREVENUECONSTITUENT
                    left join dbo.STATE on BATCHREVENUECONSTITUENT.STATEID = STATE.ID
                    left join dbo.COUNTRY on BATCHREVENUECONSTITUENT.COUNTRYID = COUNTRY.ID
                    where
                        BATCHREVENUECONSTITUENT.ID = @CONSTITUENTID
                end