USP_SIMPLEDATALIST_LOCALCORP

CREATE procedure dbo.USP_SIMPLEDATALIST_LOCALCORP 
(
  @STATEABBREV nvarchar(2) = null,
  @CITY nvarchar(255) = null
)
as

  set nocount on;

  select
      LC.ID as VALUE, 
      DS.VANITYNAME as LABEL
  from 
  (
    select distinct L1.DESIGNATIONID, D.VANITYNAME
    from dbo.LOCALCORP L1(nolock)
    inner join dbo.DESIGNATION D(nolock) on L1.DESIGNATIONID=D.ID
    where L1.STATEABBREV = @STATEABBREV and L1.CITY = @CITY and L1.ISACTIVE=1 and L1.LOCALCORP<>''
  ) DS
  cross apply
  (
    select top 1 ID
    from dbo.LOCALCORP L2(nolock)
    where L2.DESIGNATIONID = DS.DESIGNATIONID
    and L2.STATEABBREV = @STATEABBREV and L2.CITY = @CITY and L2.ISACTIVE=1 and L2.LOCALCORP<>''
  ) LC
  order by DS.VANITYNAME;