USP_SIMPLEDATALIST_ADDRESSCURRENT

                create procedure dbo.USP_SIMPLEDATALIST_ADDRESSCURRENT
                (
                    @CONSTITUENTID 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
                    where ADDRESS.CONSTITUENTID = @CONSTITUENTID 
                    and ADDRESS.HISTORICALENDDATE is null
                    order by ADDRESS.ISPRIMARY desc