UFN_CONSTITUENT_GETADDRESSES_STATEDESC

      create function dbo.UFN_CONSTITUENT_GETADDRESSES_STATEDESC
      (
        @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_GETDESCRIPTION(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