USP_MKTSEGMENTATIONEXPORTPROCESS_CREATETEMPIDTABLE

CREATE procedure dbo.[USP_MKTSEGMENTATIONEXPORTPROCESS_CREATETEMPIDTABLE]
(
  @TEMPIDTABLENAME nvarchar(128),
  @SEGMENTATIONID uniqueidentifier,
  @RECORDSOURCEID uniqueidentifier,
  @MARKETINGRECORDTYPECODE tinyint
)
with execute as owner
as
  declare @MAILINGTYPECODE tinyint;
  declare @DONORIDDATATYPE nvarchar(128);
  declare @REVENUEIDDATATYPE nvarchar(128);
  declare @MEMBERSHIPIDDATATYPE nvarchar(128);
  declare @SPONSORSHIPIDDATATYPE nvarchar(128);
  declare @COLUMNS nvarchar(max);
  -- sometimes, the finder number field from the temporary ID table has to be added to the export definition as an additional output field

  -- however, just calling it FINDERNUMBER can cause collisions with fields called FINDERNUMBER in the export definition's

  -- query views; that can cause field names to change if "use field names for one-to-one fields" is set

  -- so append the segmentation ID to make this collision unlikely

  declare @SUFFIX nvarchar(37) = '_' + replace(convert(nvarchar(36), @SEGMENTATIONID), '-', '_');

  set nocount on;

  select @MAILINGTYPECODE = [MAILINGTYPECODE] from dbo.[MKTSEGMENTATION] where [ID] = @SEGMENTATIONID;

  select
    @DONORIDDATATYPE = [DONOR].[PRIMARYKEYTYPENAME],
    @REVENUEIDDATATYPE = isnull([REVENUE].[PRIMARYKEYTYPENAME], ''),
    @MEMBERSHIPIDDATATYPE = isnull([MEMBERSHIP].[PRIMARYKEYTYPENAME], ''),
    @SPONSORSHIPIDDATATYPE = isnull([SPONSORSHIP].[PRIMARYKEYTYPENAME], '')
  from dbo.[MKTRECORDSOURCE]
  inner join dbo.[QUERYVIEWCATALOG] as [DONOR] on [DONOR].[ID] = [MKTRECORDSOURCE].[ID]
  left outer join dbo.[MKTGIFTRECORDSOURCE] on [MKTGIFTRECORDSOURCE].[ID] = [DONOR].[ID]
  left outer join dbo.[QUERYVIEWCATALOG] as [REVENUE] on [REVENUE].[ID] = [MKTGIFTRECORDSOURCE].[QUERYVIEWCATALOGID]
  left outer join dbo.[MKTMEMBERSHIPRECORDSOURCE] on [MKTMEMBERSHIPRECORDSOURCE].[ID] = [DONOR].[ID]
  left outer join dbo.[QUERYVIEWCATALOG] as [MEMBERSHIP] on [MEMBERSHIP].[ID] = [MKTMEMBERSHIPRECORDSOURCE].[QUERYVIEWCATALOGID]
  left outer join dbo.[MKTSPONSORSHIPRECORDSOURCE] on [MKTSPONSORSHIPRECORDSOURCE].[ID] = [DONOR].[ID]
  left outer join dbo.[QUERYVIEWCATALOG] as [SPONSORSHIP] on [SPONSORSHIP].[ID] = [MKTSPONSORSHIPRECORDSOURCE].[QUERYVIEWCATALOGID]
  where [MKTRECORDSOURCE].[ID] = @RECORDSOURCEID

  set @COLUMNS =
    '  [FINDERNUMBER' + @SUFFIX + '] bigint' + (case @MAILINGTYPECODE when 2 then '' else ' primary key' end) + ',' + char(13) +
    (case @MAILINGTYPECODE when 1 then '  [REVENUEID] ' + @REVENUEIDDATATYPE + ',' + char(13) else '' end) +
    (case @MAILINGTYPECODE when 2 then '  [MEMBERSHIPID] ' + @MEMBERSHIPIDDATATYPE + ',' + char(13) else '' end) +
    (case @MAILINGTYPECODE when 3 then '  [SPONSORSHIPID] ' + @SPONSORSHIPIDDATATYPE + ',' + char(13) else '' end) +
    (case @MAILINGTYPECODE when 5 then '  [REVENUEID] ' + @REVENUEIDDATATYPE + ',' + char(13) else '' end) +
    '  [DONORID] ' + @DONORIDDATATYPE + ',' + char(13) +
    (case @MAILINGTYPECODE when 2 then '  [ISMEMBER] bit,' + char(13) else '' end) +
    '  [LISTDONORID] uniqueidentifier,' + char(13) +
    '  [PACKAGEID] uniqueidentifier,' + char(13) +
    '  [WORDTEMPLATEID] uniqueidentifier,' + char(13) +
    '  [EXCLUDESPOUSE] bit not null default 0,' + char(13) +
    '  [BACKOFFICESYSTEMID] integer' +
    (case @MAILINGTYPECODE when 2 then ',' + char(13) + '  constraint PK_' + @TEMPIDTABLENAME + ' primary key clustered ([FINDERNUMBER' + @SUFFIX + '], [ISMEMBER])' else '' end);

  exec dbo.[USP_MKTSEGMENTATIONEXPORTPROCESS_CREATETABLE] @TEMPIDTABLENAME, @COLUMNS;

  return 0;