USP_SIMPLEDATALIST_DUPLICATERESOLUTIONADDRESS

                CREATE procedure dbo.USP_SIMPLEDATALIST_DUPLICATERESOLUTIONADDRESS
                (
                    @CONSTITUENTID uniqueidentifier = null
                )
                AS
                    select 
                        ADDRESS.ID as VALUE,
                        (coalesce(ADDRESSTYPECODE.DESCRIPTION + '-', '') + (case when ADDRESS.ISPRIMARY=1 then 'primary-' else '' end) +
                        case when ADDRESS.ADDRESSBLOCK = N'' then '<No address>' else coalesce(left(ADDRESS.ADDRESSBLOCK, charindex(nchar(10), ADDRESS.ADDRESSBLOCK + nchar(10)) -1), '<No address>') 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,ADDRESSTYPECODE.DESCRIPTION, ADDRESS.ADDRESSBLOCK