USP_DATAFORMTEMPLATE_EDITLOAD_MKTSEGMENTATIONSEGMENT_MULTIPLE

The load procedure used by the edit dataform template "Marketing Effort Multiple 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
@SAMPLESIZELOCKED	bit	INOUT	Sample size locked
@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.

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_MKTSEGMENTATIONSEGMENT_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,
  @SAMPLESIZELOCKED bit = null output,
  @SOURCECODEID uniqueidentifier = null output,
  @TSLONG bigint = 0 output
)
as
  set nocount on;

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

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


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

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

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

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


  /* Mailing information */
  select top 1
    @SEGMENTATIONID = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID],
    @SOURCECODEID = [MKTSEGMENTATION].[SOURCECODEID]
  from dbo.[MKTSEGMENTATIONSEGMENT]
  inner join @SEGMENTSTABLE as [S] on [S].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]
  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID];

  /* Common package */
  if (select count(distinct([PACKAGEID])) from dbo.[MKTSEGMENTATIONSEGMENT] inner join @SEGMENTSTABLE as [S] on [S].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]) = 1
    select top 1
      @PACKAGEID = [MKTSEGMENTATIONSEGMENT].[PACKAGEID],
      @PACKAGECODE = [MKTPACKAGE].[CODE],
      @PACKAGELOADED = 1
    from dbo.[MKTSEGMENTATIONSEGMENT]
    inner join @SEGMENTSTABLE as [S] on [S].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]
    inner join [MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID];
  else
    set @PACKAGELOADED = 0;

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

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

  /* Common sample size */
  if exists(select 1 from @SEGMENTSTABLE as [S] where dbo.[UFN_MKTSEGMENTATIONSEGMENT_ISVENDORMANAGEDLIST]([S].[ID]) = 1)
    begin
      set @SAMPLESIZE = 100;
      set @SAMPLESIZETYPECODE = 0;
      set @SAMPLESIZEMETHODCODE = 0;
      set @SAMPLESIZELOADED = 0;
      set @SAMPLESIZELOCKED = 1;
    end
  else
    begin
      if (select count(distinct([SAMPLESIZETYPECODE])) from dbo.[MKTSEGMENTATIONSEGMENT] inner join @SEGMENTSTABLE as [S] on [S].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]) = 1 and
         (select count(distinct([SAMPLESIZE])) from dbo.[MKTSEGMENTATIONSEGMENT] inner join @SEGMENTSTABLE as [S] on [S].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]) = 1 and
         (select count(distinct([SAMPLESIZEMETHODCODE])) from dbo.[MKTSEGMENTATIONSEGMENT] inner join @SEGMENTSTABLE as [S] on [S].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]) = 1
        select top 1
          @SAMPLESIZE = [SAMPLESIZE],
          @SAMPLESIZETYPECODE = [SAMPLESIZETYPECODE],
          @SAMPLESIZEMETHODCODE = [SAMPLESIZEMETHODCODE],
          @SAMPLESIZELOADED = 1
        from dbo.[MKTSEGMENTATIONSEGMENT]
        inner join @SEGMENTSTABLE as [S] on [S].[ID] = [MKTSEGMENTATIONSEGMENT].[ID];
      else
        begin
          set @SAMPLESIZE = 100;
          set @SAMPLESIZETYPECODE = 0;
          set @SAMPLESIZEMETHODCODE = 0;
          set @SAMPLESIZELOADED = 0;
        end
      set @SAMPLESIZELOCKED = 0;
    end

  select @TSLONG = max([TSLONG])
  from dbo.[MKTSEGMENTATIONSEGMENT]
  inner join @SEGMENTSTABLE as [S] on [S].[ID] = [MKTSEGMENTATIONSEGMENT].[ID];

  -- TODO:  this should not always return 1

  set @DATALOADED = 1;

  return 0;