USP_DATALIST_DIRECTMARKETINGEFFORTBATCHLIST

CREATE procedure dbo.[USP_DATALIST_DIRECTMARKETINGEFFORTBATCHLIST]
(
  @BATCHID uniqueidentifier,
  @LISTID uniqueidentifier
)
as
  set nocount on;

  if exists(select * from dbo.[MKTLIST] where [ID] = @LISTID)
    select top 1
      cast(1 as bit) as [EXISTINGLIST],
      [MKTLIST].[NAME],
      [MKTLIST].[DESCRIPTION],
      [MKTSOURCECODEPARTDEFINITIONVALUES].[ID] as [CODEVALUEID],
      [MKTSOURCECODEPARTDEFINITIONVALUES].[DESCRIPTION] as [CODEVALUE],
      (select [FORMAT] from dbo.[MKTSOURCECODEPARTDEFINITIONVALUES] where [ID] = [MKTLIST].[PARTDEFINITIONVALUESID]) as [CODEFORMAT],
      (select [REGEX] from dbo.[MKTSOURCECODEPARTDEFINITIONVALUES] where [ID] = [MKTLIST].[PARTDEFINITIONVALUESID]) as [CODEREGEX],
      [MKTLIST].[CODE],
      [MKTLISTCATEGORYCODE].[ID] as [CATEGORYCODEID],
      [MKTLISTCATEGORYCODE].[DESCRIPTION] as [CATEGORY],
      [MKTLIST].[VENDORID],
      [CONSTITUENT].[NAME] as [VENDOR],
      dbo.[UFN_TRANSLATIONFUNCTION_SITE_GETNAME]([MKTLIST].[SITEID]) as [SITE],
      [MKTLIST].[SITEID]
    from dbo.[MKTLIST]
    left join dbo.[MKTSOURCECODEPARTDEFINITIONVALUES] on [MKTSOURCECODEPARTDEFINITIONVALUES].[ID] = [MKTLIST].[PARTDEFINITIONVALUESID]
    left join dbo.[MKTLISTCATEGORYCODE] on [MKTLISTCATEGORYCODE].[ID] = [MKTLIST].[LISTCATEGORYCODEID]
    left join dbo.[CONSTITUENT] on [CONSTITUENT].[ID] = [MKTLIST].[VENDORID]
    where [MKTLIST].[ID] = @LISTID;
  else
    select top 1
      cast(0 as bit) as [EXISTINGLIST],
      [BATCHDIRECTMARKETINGEFFORTLIST].[NAME],
      [BATCHDIRECTMARKETINGEFFORTLIST].[DESCRIPTION],
      [MKTSOURCECODEPARTDEFINITIONVALUES].[ID] as [CODEVALUEID],
      [MKTSOURCECODEPARTDEFINITIONVALUES].[DESCRIPTION] as [CODEVALUE],
      (select [FORMAT] from dbo.[MKTSOURCECODEPARTDEFINITIONVALUES] where [ID] = [BATCHDIRECTMARKETINGEFFORTLIST].[CODEPARTDEFINITIONVALUESID]) as [CODEFORMAT],
      (select [REGEX] from dbo.[MKTSOURCECODEPARTDEFINITIONVALUES] where [ID] = [BATCHDIRECTMARKETINGEFFORTLIST].[CODEPARTDEFINITIONVALUESID]) as [CODEREGEX],
      [BATCHDIRECTMARKETINGEFFORTLIST].[CODE],
      [MKTLISTCATEGORYCODE].[ID] as [CATEGORYCODEID],
      [MKTLISTCATEGORYCODE].[DESCRIPTION] as [CATEGORY],
      [BATCHDIRECTMARKETINGEFFORTLIST].[VENDORID],
      [CONSTITUENT].[NAME] as [VENDOR],
      dbo.[UFN_TRANSLATIONFUNCTION_SITE_GETNAME]([BATCHDIRECTMARKETINGEFFORTLIST].[SITEID]) as [SITE],
      [BATCHDIRECTMARKETINGEFFORTLIST].[SITEID]
    from dbo.[BATCHDIRECTMARKETINGEFFORT]
    inner join dbo.[BATCHDIRECTMARKETINGEFFORTLIST] on [BATCHDIRECTMARKETINGEFFORTLIST].[ID] = [BATCHDIRECTMARKETINGEFFORT].[BATCHDIRECTMARKETINGEFFORTLISTID]
    left join dbo.[MKTSOURCECODEPARTDEFINITIONVALUES] on [MKTSOURCECODEPARTDEFINITIONVALUES].[ID] = [BATCHDIRECTMARKETINGEFFORTLIST].[CODEPARTDEFINITIONVALUESID]
    left join dbo.[MKTLISTCATEGORYCODE] on [MKTLISTCATEGORYCODE].[ID] = [BATCHDIRECTMARKETINGEFFORTLIST].[MKTLISTCATEGORYCODEID]
    left join dbo.[CONSTITUENT] on [CONSTITUENT].[ID] = [BATCHDIRECTMARKETINGEFFORTLIST].[VENDORID]
    where [BATCHDIRECTMARKETINGEFFORT].[BATCHID] = @BATCHID
    and [BATCHDIRECTMARKETINGEFFORTLIST].[ID] = @LISTID;

  return 0;