USP_SIMPLEDATALIST_TAXDECLARATIONADDRESS

create procedure dbo.USP_SIMPLEDATALIST_TAXDECLARATIONADDRESS
(
    @CONSTITUENTID uniqueidentifier = null,
    @TAXDECLARATIONID uniqueidentifier = null
)
AS
    select 
        ADDRESS.ID as VALUE,
        (coalesce(ADDRESSTYPECODE.DESCRIPTION + ' ', '') + '(' + case when ADDRESS.ADDRESSBLOCK = N'' then '<No address>' else coalesce(left(ADDRESS.ADDRESSBLOCK, charindex(nchar(10), ADDRESS.ADDRESSBLOCK + nchar(10)) -1), '<No address>') end + 
            (case when ADDRESS.ISPRIMARY=1 then '; Primary' else '' end) + ')') as LABEL
    from ADDRESS
    left outer join ADDRESSTYPECODE on ADDRESSTYPECODE.ID = ADDRESS.ADDRESSTYPECODEID
    left join dbo.TAXDECLARATION on TAXDECLARATION.ID = @TAXDECLARATIONID and TAXDECLARATION.ADDRESSID = ADDRESS.ID
    where ADDRESS.CONSTITUENTID = @CONSTITUENTID
    and (
        ADDRESS.HISTORICALENDDATE is null or
        not TAXDECLARATION.ADDRESSID is null
    )
    order by ADDRESS.ISPRIMARY desc;