USP_ADDRESS_CREATE_PRELOAD

            CREATE procedure dbo.USP_ADDRESS_CREATE_PRELOAD (
                @CONSTITUENTID uniqueidentifier,
                @PRIMARY bit = null output,
                @COUNTRYID uniqueidentifier = null output,
                @SPOUSENAME nvarchar(700) = null output,
                @VALIDATIONCOUNTRIES xml = null output,
                @ZIPLOOKUPCOUNTRIES xml = null output
            ) as 
                set nocount on;

                exec @COUNTRYID = dbo.UFN_COUNTRY_GETDEFAULT;

                if exists(select object_id from sys.objects where type = 'U' and name = 'RELATIONSHIP')
                    select
                        @PRIMARY = case when count(ADDRESS.ID) = 0 then 1 else 0 end,
                        @SPOUSENAME = SPOUSE_NF.NAME
                    from
                        dbo.CONSTITUENT
                    left join dbo.ADDRESS on ADDRESS.CONSTITUENTID = CONSTITUENT.ID and ADDRESS.ISPRIMARY = 1
                    left join dbo.RELATIONSHIP on RELATIONSHIP.RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID and RELATIONSHIP.ISSPOUSE = 1
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(RELATIONSHIP.RECIPROCALCONSTITUENTID) SPOUSE_NF
                    where
                        CONSTITUENT.ID = @CONSTITUENTID
                    group by
                        SPOUSE_NF.NAME;
                else
                    select
                        @PRIMARY = case when count(ADDRESS.ID) = 0 then 1 else 0 end
                    from
                        dbo.ADDRESS
                    where
                        ADDRESS.CONSTITUENTID = @CONSTITUENTID;

                select @VALIDATIONCOUNTRIES = dbo.UFN_COUNTRY_GETVALIDATIONCOUNTRIES_TOITEMLISTXML();
                select @ZIPLOOKUPCOUNTRIES = dbo.UFN_ZIPCITYSTATE_GETCOUNTRIES_TOITEMLISTXML();

                return 0;