USP_DUPLICATESEARCH_MKTSEGMENTGENERATE

CREATE procedure dbo.[USP_DUPLICATESEARCH_MKTSEGMENTGENERATE]
(
  @SEGMENTS xml = null,
  @SEGMENTTYPECODE tinyint,
  @MAXROWS smallint = 100
)
as
  set nocount on;

  declare @NAME nvarchar(100);
  declare @CODE nvarchar(10);
  declare @SELECTIONS xml;

  declare @TEMP table (
    [ID] uniqueidentifier,
    [NAME] nvarchar(100),
    [DESCRIPTION] nvarchar(255),
    [CODE] nvarchar(10),
    [INUSE] bit,
    [DUPLICATESELECTION] bit,
    [DUPLICATECODE] bit
  );

  declare @DUPLICATES table (
    [SEGMENTNAME] nvarchar(100),
    [SEGMENTCODE] nvarchar(50),
    [DUPLICATEID] uniqueidentifier,
    [DUPLICATENAME] nvarchar(100),
    [DUPLICATEDESCRIPTION] nvarchar(255),
    [DUPLICATECODE] nvarchar(10),
    [DUPLICATEINUSE] bit,
    [HASDUPLICATESELECTION] bit,
    [HASDUPLICATECODE] bit
  );

  if not @SEGMENTS is null
    begin
      declare SEGMENTS cursor local fast_forward for
        select 
          T.c.value('(SEGMENTNAME)[1]','nvarchar(100)') as [NAME],
          T.c.value('(SEGMENTCODE)[1]','nvarchar(50)') as [CODE],
          T.c.query('SELECTIONS') as [SELECTIONS]
        from @SEGMENTS.nodes('/SEGMENTS/ITEM') T(c);

      open SEGMENTS;
      fetch next from SEGMENTS into @NAME, @CODE, @SELECTIONS;

      while (@@FETCH_STATUS = 0)
      begin
        delete from @TEMP;

        --Find any duplicates for this segment...

        insert into @TEMP ([ID], [NAME], [DESCRIPTION], [CODE], [INUSE], [DUPLICATESELECTION], [DUPLICATECODE])
          exec dbo.[USP_MKTSEGMENT_FINDDUPLICATES]
            @SEGMENTTYPECODE = @SEGMENTTYPECODE,
            @CODE = @CODE,
            @SELECTIONS = @SELECTIONS,
            @MAXROWS = @MAXROWS;

        --Insert the duplicates into our return table...

        insert into @DUPLICATES ([SEGMENTNAME], [SEGMENTCODE], [DUPLICATEID], [DUPLICATENAME], [DUPLICATEDESCRIPTION], [DUPLICATECODE], [DUPLICATEINUSE], [HASDUPLICATESELECTION], [HASDUPLICATECODE])
          select
            @NAME,
            @CODE,
            [ID],
            [NAME],
            [DESCRIPTION],
            [CODE],
            [INUSE],
            [DUPLICATESELECTION],
            [DUPLICATECODE]
          from @TEMP;

        fetch next from SEGMENTS into @NAME, @CODE, @SELECTIONS;
      end;

      close SEGMENTS;
      deallocate SEGMENTS;
    end

  --Return all the duplicates...

  select
    [SEGMENTNAME],
    [SEGMENTCODE],
    [DUPLICATEID],
    [DUPLICATENAME],
    [DUPLICATEDESCRIPTION],
    [DUPLICATECODE],
    [DUPLICATEINUSE],
    [HASDUPLICATESELECTION],
    [HASDUPLICATECODE]
  from @DUPLICATES;

  return 0;