USP_DATALIST_CONSTITUENTFILEIMPORTCOUNTS

CREATE procedure dbo.[USP_DATALIST_CONSTITUENTFILEIMPORTCOUNTS]
(
  @SEGMENTATIONID uniqueidentifier
)
with execute as owner
as
  set nocount on;

  declare @TEMP table ([EFFORTNAME] nvarchar(255), [SEGMENTNAME] nvarchar(255), [SOURCECODE] nvarchar(255), [PACKAGEID] uniqueidentifier, [PACKAGENAME] nvarchar(255), [QUANTITY] int);
  declare @SQL nvarchar(max)
  declare @DATATABLE nvarchar(256);
  declare @EFFORTNAME nvarchar(100);
  declare @ACTIVE bit;

  set @DATATABLE = dbo.[UFN_MKTCONSTITUENTFILEIMPORT_BUILDTABLENAME](@SEGMENTATIONID);
  select @EFFORTNAME = [NAME], @ACTIVE = [ACTIVE] from dbo.[MKTSEGMENTATION] where [ID] = @SEGMENTATIONID;

  if exists(select 1 from INFORMATION_SCHEMA.TABLES where (TABLE_SCHEMA = 'dbo') and (TABLE_NAME = @DATATABLE))
  begin
    set @SQL = 'select' + char(13) +
               '  @EFFORTNAME [EFFORTNAME],' + char(13) +
               '  isnull([MKTSEGMENTATIONTESTSEGMENT].[NAME], [MKTSEGMENT].[NAME]) [SEGMENTNAME],' + char(13) +
               '  [DATA].[SOURCECODE] [SOURCECODE],' + char(13) +
               '  isnull([P2].[ID], [P1].[ID]) [PACKAGEID],' + char(13) +
               '  isnull([P2].[NAME], [P1].[NAME]) [PACKAGENAME],' + char(13) +
               '  count(*) [QUANTITY]' + char(13) +
               'from dbo.[' + @DATATABLE + '] [DATA]' + char(13) +
               'left join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [DATA].[SEGMENTATIONSEGMENTID]' + char(13) +
               'left join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]' + char(13) +
               'left join dbo.[MKTSEGMENTATIONTESTSEGMENT] [MKTSEGMENTATIONTESTSEGMENT] on [MKTSEGMENTATIONTESTSEGMENT].[ID] = [DATA].[SEGMENTATIONTESTSEGMENTID]' + char(13) +
               'left join dbo.[MKTPACKAGE] [P1] on [P1].[ID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]' + char(13) +
               'left join dbo.[MKTPACKAGE] [P2] on [P2].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID]' + char(13) +
               'group by [MKTSEGMENTATIONTESTSEGMENT].[NAME], [MKTSEGMENT].[NAME], [DATA].[SOURCECODE], [P2].[ID], [P1].[ID], [P2].[NAME], [P1].[NAME];';
    insert into @TEMP
      exec sp_executesql @SQL, N'@EFFORTNAME nvarchar(100), @SEGMENTATIONID uniqueidentifier',@EFFORTNAME = @EFFORTNAME, @SEGMENTATIONID = @SEGMENTATIONID;
  end

  if exists (select 1 from @TEMP)
    select  
      [EFFORTNAME],
      [SEGMENTNAME],
      [SOURCECODE],
      [PACKAGEID],
      [PACKAGENAME],
      [QUANTITY]
    from @TEMP;
  else
    select @EFFORTNAME [EFFORTNAME], null [SEGMENTNAME], null [SOURCECODE], null [PACKAGEID], null [PACKAGETNAME], 0 [QUANTITY]

  return 0;