UFN_MKTSEGMENTLIST_DUPLICATEFIELDSEXIST

create function dbo.[UFN_MKTSEGMENTLIST_DUPLICATEFIELDSEXIST]
(
  @SEGMENTLISTID uniqueidentifier
)
returns tinyint
as
begin
  declare @RESULT tinyint;
  declare @RECORDSOURCEID uniqueidentifier;
  declare @LAYOUTID uniqueidentifier;
  declare @DUPLICATEID uniqueidentifier;
  declare @DEDUPLISTS bit;

  set @RESULT = 2;   /* 0=False, 1=True, 2=No duplicate fields defined, 3=Skip dedup */

  select
    @RECORDSOURCEID = [MKTLIST].[RECORDSOURCEID],
    @LAYOUTID = [MKTSEGMENTLIST].[LISTLAYOUTID]
  from dbo.[MKTSEGMENTLIST]
  inner join dbo.[MKTLIST] on [MKTLIST].[ID] = [MKTSEGMENTLIST].[LISTID]
  where [MKTSEGMENTLIST].[ID] = @SEGMENTLISTID;

  select
    @DUPLICATEID = [ID],
    @DEDUPLISTS = [DEDUPLISTS]
  from dbo.[MKTDUPLICATE]
  where [QUERYVIEWCATALOGID] = @RECORDSOURCEID;

  if @DEDUPLISTS = 1
    --Compare the duplicate fields against those in the layout.  All duplicate fields

    --must exist in the layout, otherwise we cannot process duplicates.

    set @RESULT = (case when exists(
                     select 1
                     from dbo.[MKTDUPLICATEFIELD]
                     left join dbo.[MKTLISTLAYOUTFIELD]
                        on upper([MKTLISTLAYOUTFIELD].[FIELDNAME]) = upper([MKTDUPLICATEFIELD].[FIELDNAME])
                        and [MKTLISTLAYOUTFIELD].[LISTLAYOUTID] = @LAYOUTID
                        and [MKTLISTLAYOUTFIELD].[MAPPINGCODE] = 1
                        and exists(select 1 from [INFORMATION_SCHEMA].[COLUMNS] where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = 'MKTSEGMENTLISTDATA' and upper([COLUMN_NAME]) = upper([MKTLISTLAYOUTFIELD].[FIELDNAME]))
                     where [MKTDUPLICATEFIELD].[DUPLICATEID] = @DUPLICATEID
                     and [MKTLISTLAYOUTFIELD].[ID] is null)
                   then 0 else 1 end);
  else
    set @RESULT = 3;

  return @RESULT;
end