USP_DATALIST_FINDERNUMBERLOOKUP

CREATE procedure dbo.[USP_DATALIST_FINDERNUMBERLOOKUP]
(
  @FINDERNUMBER bigint = null
)
as
  set nocount on;

  declare @LOCALFINDERNUMBER bigint = @FINDERNUMBER;

  select top 1
    [CONSTITUENT].[ID],
    [CONSTITUENT].[NAME],
    [CONSTITUENTAPPEAL].[APPEALID],
    isnull((select [NAME] from dbo.[APPEAL] where [ID] = [CONSTITUENTAPPEAL].[APPEALID]), '') as [APPEALNAME],
    [CONSTITUENTAPPEAL].[SOURCECODE],
    [CONSTITUENTAPPEAL].[MKTSEGMENTATIONID] as [MAILINGID],
    isnull((select [NAME] from dbo.[MKTSEGMENTATION] where [ID] = [CONSTITUENTAPPEAL].[MKTSEGMENTATIONID]), '') as [MAILINGNAME],
    [CONSTITUENT].[LOOKUPID] as [CONSTITUENTLOOKUPID],
    [MKTPACKAGE].[NAME] as PACKAGE,
    (case
      when [MKTSEGMENTATIONTESTSEGMENT].[ID] is not null then dbo.[UFN_MKTSEGMENTATIONTESTSEGMENT_GETNAME]([MKTSEGMENTATIONTESTSEGMENT].[ID])
      when [MKTSEGMENTATIONSEGMENT].[ID] is not null then isnull((select [NAME] from dbo.[MKTSEGMENT] where [ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]), '')
      else ''
     end) as [SEGMENT],
    (case
      when [MAILLETTER].[ID] is not null then [MAILLETTER].[NAME] + ' (Mail)'
      when [EMAILLETTER].[ID] is not null then [EMAILLETTER].[NAME] + ' (Email)'
      else ''
     end) as [LETTER]
  from dbo.[CONSTITUENTAPPEAL]
  inner join dbo.[CONSTITUENT] on [CONSTITUENT].[ID] = [CONSTITUENTAPPEAL].[CONSTITUENTID]
  left join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [CONSTITUENTAPPEAL].[MKTSEGMENTATIONSEGMENTID]
  left join dbo.[MKTSEGMENTATIONTESTSEGMENT] on [MKTSEGMENTATIONTESTSEGMENT].[ID] = [CONSTITUENTAPPEAL].[MKTSEGMENTATIONTESTSEGMENTID]
  left join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = coalesce([CONSTITUENTAPPEAL].[MKTPACKAGEID], [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID], [MKTSEGMENTATIONSEGMENT].[PACKAGEID])
  left join dbo.[APPEALMAILINGSETUPLETTER] as [MAILLETTER] on [MAILLETTER].[APPEALMAILINGSETUPID] = [CONSTITUENTAPPEAL].[MKTSEGMENTATIONID] and [MAILLETTER].[MAILPACKAGEID] = [CONSTITUENTAPPEAL].[MKTPACKAGEID]
  left join dbo.[APPEALMAILINGSETUPLETTER] as [EMAILLETTER] on [EMAILLETTER].[APPEALMAILINGSETUPID] = [CONSTITUENTAPPEAL].[MKTSEGMENTATIONID] and [EMAILLETTER].[EMAILPACKAGEID] = [CONSTITUENTAPPEAL].[MKTPACKAGEID]
  where [CONSTITUENTAPPEAL].[FINDERNUMBER] = @LOCALFINDERNUMBER
  order by [CONSTITUENTAPPEAL].[DATEADDED] desc
  option (RECOMPILE);

  return 0;