USP_DATAFORMTEMPLATE_VIEW_SEGMENTATIONSEGMENTCOPYDUPLICATECHECK

create procedure dbo.[USP_DATAFORMTEMPLATE_VIEW_SEGMENTATIONSEGMENTCOPYDUPLICATECHECK]
(
  @ID nvarchar(73), --source | target

  @DATALOADED bit = 0 output,
  @SOURCEEFFORTNAME nvarchar(100) = null output,
  @TARGETEFFORTNAME nvarchar(100) = null output,
  @TOTALSOURCESEGMENTS int = null output,
  @TOTALDUPLICATESEGMENTS int = null output,
  @DUPLICATENAMESLIST nvarchar(max) = null output
)
as
  set nocount on;

  -- be sure to set this, in case the select returns no rows

  set @DATALOADED = 0;

  -- extract IDs

  declare @SOURCEEFFORTID as uniqueidentifier = convert(uniqueidentifier, substring(@ID, 1, charindex('|', @ID, 1) - 1));
  declare @TARGETEFFORTID as uniqueidentifier = convert(uniqueidentifier, substring(@ID, charindex('|', @ID, 1) + 1, len(@ID)));

  with [SOURCEEFFORTSEGMENTS_CTE] as (
    select
      [MKTSEGMENTATIONSEGMENT].[SEGMENTID],
      [MKTSEGMENTATIONSEGMENT].[PACKAGEID],
      [MKTSEGMENTATIONSEGMENT].[SEQUENCE]
    from
      dbo.[MKTSEGMENTATIONSEGMENT]
    where
      [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SOURCEEFFORTID
  ),
  [DUPLICATESEGMENTS_CTE] as (
    select
      [MKTSEGMENTATIONSEGMENT].[PACKAGEID],
      [MKTSEGMENTATIONSEGMENT].[SEGMENTID],
      [MKTSEGMENT].[NAME],
      [SOURCEEFFORTSEGMENTS_CTE].[SEQUENCE]
    from
      dbo.[MKTSEGMENTATIONSEGMENT]
      inner join [SOURCEEFFORTSEGMENTS_CTE] on [SOURCEEFFORTSEGMENTS_CTE].[PACKAGEID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID] and [SOURCEEFFORTSEGMENTS_CTE].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
      inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
    where
      [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @TARGETEFFORTID
  )
  select
    @DATALOADED = 1,
    @SOURCEEFFORTNAME = (select [NAME] from dbo.[MKTSEGMENTATION] where [ID] = @SOURCEEFFORTID),
    @TARGETEFFORTNAME = (select [NAME] from dbo.[MKTSEGMENTATION] where [ID] = @TARGETEFFORTID),
    @TOTALSOURCESEGMENTS = (select count(1) from [SOURCEEFFORTSEGMENTS_CTE]),
    @TOTALDUPLICATESEGMENTS = (select count(1) from [DUPLICATESEGMENTS_CTE]),
    @DUPLICATENAMESLIST = stuff((select top 5 char(13) + [DUPLICATESEGMENTS_CTE].[NAME] from [DUPLICATESEGMENTS_CTE] order by [DUPLICATESEGMENTS_CTE].[SEQUENCE] asc for xml path(''), type).value('.', 'varchar(max)'), 1, 1, '');

  return 0;