USP_DATAFORMTEMPLATE_EDITLOAD_MKTSEGMENTATIONTESTSEGMENT_MULTIPLE

The load procedure used by the edit dataform template "Marketing Effort Multiple Test Segment Edit Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	nvarchar(max)	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@SEGMENTATIONID	uniqueidentifier	INOUT	Marketing effort
@PACKAGEID	uniqueidentifier	INOUT	Package
@PACKAGECODE	nvarchar(10)	INOUT	Package code
@PACKAGELOADED	bit	INOUT	Package loaded
@RESPONSERATE	decimal(5, 2)	INOUT	Response rate
@RESPONSERATELOADED	bit	INOUT	Response rate loaded
@GIFTAMOUNT	money	INOUT	Gift amount
@GIFTAMOUNTLOADED	bit	INOUT	Gift amount loaded
@SAMPLESIZE	int	INOUT	Sample size
@SAMPLESIZETYPECODE	tinyint	INOUT	Sample size type
@SAMPLESIZEMETHODCODE	tinyint	INOUT	Sample size method
@SAMPLESIZELOADED	bit	INOUT	Sample size loaded
@SOURCECODEID	uniqueidentifier	INOUT	Source code ID
@TSLONG	bigint	INOUT	Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record.
@MAILINGTYPECODE	tinyint	INOUT	Marketing effort type code

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_MKTSEGMENTATIONTESTSEGMENT_MULTIPLE
(
  @ID nvarchar(max),
  @DATALOADED bit = 0 output,
  @SEGMENTATIONID uniqueidentifier = null output,
  @PACKAGEID uniqueidentifier = null output,
  @PACKAGECODE nvarchar(10) = null output,
  @PACKAGELOADED bit = null output,
  @RESPONSERATE decimal(5,2) = null output,
  @RESPONSERATELOADED bit = null output,
  @GIFTAMOUNT money = null output,
  @GIFTAMOUNTLOADED bit = null output,
  @SAMPLESIZE int = null output,
  @SAMPLESIZETYPECODE tinyint = null output,
  @SAMPLESIZEMETHODCODE tinyint = null output,
  @SAMPLESIZELOADED bit = null output,
  @SOURCECODEID uniqueidentifier = null output,
  @TSLONG bigint = 0 output,
  @MAILINGTYPECODE tinyint = null output
)
as
  set nocount on;

  set @DATALOADED = 0;
  set @TSLONG = 0;

  declare @TESTSEGMENTSTABLE table([ID] uniqueidentifier);
  declare @START int;
  declare @POS int;

  /* Parse out the multiple test segment IDs */
  set @START = 0;
  select @POS = charindex(',', @ID, @START);

  while (@POS <> 0)
  begin
    insert into @TESTSEGMENTSTABLE
      select substring(@ID, @START, @POS - @START);

    set @START = @POS + 1;
    select @POS = charindex(',', @ID, @START);
  end;

  if len(@ID) > 0
    insert into @TESTSEGMENTSTABLE
      select substring(@ID, @START, 37);


  /* Mailing information */
  select top 1
    @SEGMENTATIONID = [MKTSEGMENTATION].[ID],
    @SOURCECODEID = [MKTSEGMENTATION].[SOURCECODEID],
    @MAILINGTYPECODE = [MKTSEGMENTATION].[MAILINGTYPECODE]
  from dbo.[MKTSEGMENTATIONTESTSEGMENT]
  inner join @TESTSEGMENTSTABLE as [T] on [T].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[ID]
  inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID]
  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID];

  /* Check if the mailing is currently being activated */
  declare @R int;
  exec @R = dbo.[USP_MKTSEGMENTATION_CHECKACTIVATION] @SEGMENTATIONID;
  if @R <> 0
    return 1;

  /* Common package */
  if @MAILINGTYPECODE <> 1 and (select count(distinct([PACKAGEID])) from dbo.[MKTSEGMENTATIONTESTSEGMENT] inner join @TESTSEGMENTSTABLE as [T] on [T].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[ID]) = 1
    select top 1
      @PACKAGEID = [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID],
      @PACKAGECODE = [MKTPACKAGE].[CODE],
      @PACKAGELOADED = 1
    from dbo.[MKTSEGMENTATIONTESTSEGMENT]
    inner join @TESTSEGMENTSTABLE as [T] on [T].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[ID]
    inner join [MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID];
  else
    set @PACKAGELOADED = 0;

  /* Common response rate */
  if (select count(distinct([RESPONSERATE])) from dbo.[MKTSEGMENTATIONTESTSEGMENT] inner join @TESTSEGMENTSTABLE as [T] on [T].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[ID]) = 1
    select top 1
      @RESPONSERATE = [RESPONSERATE],
      @RESPONSERATELOADED = 1
    from dbo.[MKTSEGMENTATIONTESTSEGMENT]
    inner join @TESTSEGMENTSTABLE as [T] on [T].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[ID];
  else
    begin
      set @RESPONSERATE = 5;
      set @RESPONSERATELOADED = 0;
    end

  /* Common gift amount */
  if (select count(distinct([GIFTAMOUNT])) from dbo.[MKTSEGMENTATIONTESTSEGMENT] inner join @TESTSEGMENTSTABLE as [T] on [T].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[ID]) = 1
    select top 1
      @GIFTAMOUNT = [GIFTAMOUNT],
      @GIFTAMOUNTLOADED = 1
    from dbo.[MKTSEGMENTATIONTESTSEGMENT]
    inner join @TESTSEGMENTSTABLE as [T] on [T].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[ID];
  else
    set @GIFTAMOUNTLOADED = 0;

  /* Common sample size */
  if (select count(distinct([SAMPLESIZETYPECODE])) from dbo.[MKTSEGMENTATIONTESTSEGMENT] inner join @TESTSEGMENTSTABLE as [T] on [T].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[ID]) = 1 and
     (select count(distinct([SAMPLESIZE])) from dbo.[MKTSEGMENTATIONTESTSEGMENT] inner join @TESTSEGMENTSTABLE as [T] on [T].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[ID]) = 1 and
     (select count(distinct([SAMPLESIZEMETHODCODE])) from dbo.[MKTSEGMENTATIONTESTSEGMENT] inner join @TESTSEGMENTSTABLE as [T] on [T].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[ID]) = 1
    select top 1
      @SAMPLESIZE = [SAMPLESIZE],
      @SAMPLESIZETYPECODE = [SAMPLESIZETYPECODE],
      @SAMPLESIZEMETHODCODE = [SAMPLESIZEMETHODCODE],
      @SAMPLESIZELOADED = 1
    from dbo.[MKTSEGMENTATIONTESTSEGMENT]
    inner join @TESTSEGMENTSTABLE as [T] on [T].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[ID];
  else
    begin
      set @SAMPLESIZE = 100;
      set @SAMPLESIZETYPECODE = 0;
      set @SAMPLESIZEMETHODCODE = 0;
      set @SAMPLESIZELOADED = 0;
    end

  select @TSLONG = max([TSLONG])
  from dbo.[MKTSEGMENTATIONTESTSEGMENT]
  inner join @TESTSEGMENTSTABLE as [T] on [T].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[ID];

  -- TODO:  this should not always return 1

  set @DATALOADED = 1;

  return 0;