UFN_CONSTITUENT_GETADDRESSES

            CREATE function dbo.UFN_CONSTITUENT_GETADDRESSES
            (
                @INCOMINGID nvarchar(37)
            )
            returns @OUTPUT table(ID uniqueidentifier, ADDRESSBLOCK nvarchar(150), CITY nvarchar(50), STATE nvarchar(100), POSTCODE nvarchar(12), FULLADDRESS nvarchar(500), COUNTRY nvarchar(100))
            as
            begin
                declare @ID uniqueidentifier;
                declare @TYPE tinyint;
                declare @CONSTITUENTID uniqueidentifier;
                declare @RELATIONSHIPID uniqueidentifier;

                set @ID = substring(@INCOMINGID,1,36);
                set @TYPE = substring(@INCOMINGID,37,1)

                if @TYPE = 0
                begin
                    select @CONSTITUENTID = CONSTITUENTID
                    from dbo.ADDRESS
                    where ID = @ID;
                end
                if @TYPE = 1
                begin
                    select @CONSTITUENTID = CONSTITUENT.ID
                    from dbo.CONSTITUENT
                    where CONSTITUENT.ID = @ID;
                end
                if @TYPE = 2
                begin
                    select @RELATIONSHIPID = RELATIONSHIP.ID,
                        @CONSTITUENTID = RELATIONSHIP.RELATIONSHIPCONSTITUENTID
                    from dbo.RELATIONSHIP
                    inner join dbo.CONSTITUENT on CONSTITUENT.ID = RELATIONSHIP.RELATIONSHIPCONSTITUENTID
                    where RELATIONSHIP.ID = @ID;
                end

                insert into @OUTPUT(ID, ADDRESSBLOCK, CITY, STATE, POSTCODE, FULLADDRESS, COUNTRY)
                (select ID,
                    ADDRESSBLOCK,
                    CITY,
                    dbo.UFN_STATE_GETABBREVIATION(STATEID) as STATE,
                    POSTCODE,
                    dbo.UFN_BUILDFULLADDRESS(ID, ADDRESSBLOCK, CITY, STATEID, POSTCODE, COUNTRYID) as FULLADDRESS,
                    dbo.UFN_COUNTRY_GETDESCRIPTION(COUNTRYID) as COUNTRY
                from dbo.ADDRESS
                where CONSTITUENTID = @CONSTITUENTID
                    or RELATIONSHIPID = @RELATIONSHIPID);

                return;
            end