USP_SIMPLEDATALIST_CONSTITUENTLETTERADDRESS

                CREATE procedure dbo.USP_SIMPLEDATALIST_CONSTITUENTLETTERADDRESS
                (
                    @INCOMINGID nvarchar(37)
                )
                as
                    set nocount on;

                    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 or @TYPE =''
                    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

                    select ADDRESS.ID as VALUE,
                        case when RELATIONSHIPID is not null then '(Contact) - ' else (case when ISPRIMARY = 1 then '(Primary) - ' else '' end) end
                        + coalesce(dbo.UFN_ADDRESSTYPECODE_GETDESCRIPTION(ADDRESSTYPECODEID),'')
                        + case when ADDRESSTYPECODEID is not null then ' - ' else '' end
                        + case
                            when len(ADDRESSBlOCK) > 0 then substring(ADDRESSBLOCK, 0, case when charindex(char(10),ADDRESSBLOCK) = 0 then len(addressblock) + 1 else charindex(char(10),ADDRESSBLOCK) end)
                            when len(CITY) > 0 then CITY
                            when STATEID is not null then dbo.UFN_STATE_GETABBREVIATION(STATEID)
                            when len(POSTCODE) > 0 then POSTCODE
                        end as LABEL
                    from dbo.ADDRESS
                    where (CONSTITUENTID = @CONSTITUENTID or RELATIONSHIPID = @RELATIONSHIPID)
                        and (len(ADDRESSBLOCK) > 0 or len(CITY) > 0 or STATEID is not null or len(POSTCODE) > 0)
                        and DONOTMAIL = 0;