USP_DATAFORMTEMPLATE_EDIT_MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE_MINIMAL_MULTIPLE

The save procedure used by the edit dataform template "Marketing Acknowledgement Template Multiple Rule Minimal Edit Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	nvarchar(max)	IN	The input ID parameter indicating the ID of the record being edited.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@ASKLADDER	xml	IN	Ask ladder
@ASKLADDERLOADED	bit	IN	Ask ladder loaded
@PACKAGEID	uniqueidentifier	IN	Package
@PACKAGECODE	nvarchar(10)	IN	Package code
@PACKAGELOADED	bit	IN	Package loaded
@RESPONSERATE	decimal(5, 2)	IN	Response rate
@RESPONSERATELOADED	bit	IN	Response rate loaded
@GIFTAMOUNT	money	IN	Gift amount
@GIFTAMOUNTLOADED	bit	IN	Gift amount loaded
@MAXDAYS	int	IN	Maximum days
@MAXDAYSCHECKED	bit	IN	Process transactions within
@MAXDAYSLOADED	bit	IN	Max days loaded
@MINQUANTITY	int	IN	Minimum quantity
@MINQUANTITYCHECKED	bit	IN	Process when the number of transactions reaches
@MINQUANTITYLOADED	bit	IN	Min quantity loaded

Definition

Copy


CREATE procedure dbo.[USP_DATAFORMTEMPLATE_EDIT_MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE_MINIMAL_MULTIPLE]
(
  @ID nvarchar(max),
  @CHANGEAGENTID uniqueidentifier = null,
  @ASKLADDER xml,
  @ASKLADDERLOADED bit,
  @PACKAGEID uniqueidentifier,
  @PACKAGECODE nvarchar(10),
  @PACKAGELOADED bit,
  @RESPONSERATE decimal(5,2),
  @RESPONSERATELOADED bit,
  @GIFTAMOUNT money,
  @GIFTAMOUNTLOADED bit,
  @MAXDAYS int,
  @MAXDAYSCHECKED bit,
  @MAXDAYSLOADED bit,
  @MINQUANTITY int,
  @MINQUANTITYCHECKED bit,
  @MINQUANTITYLOADED bit
)
as
  set nocount on;

  declare @RULESTABLE table([ID] uniqueidentifier);
  declare @START int;
  declare @POS int;
  declare @RULEID uniqueidentifier;
  declare @CURRENTDATE datetime;

  begin try
    if @CHANGEAGENTID is null  
      exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;

    set @CURRENTDATE = GetDate();

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

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

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

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

    declare RULECURSOR cursor local fast_forward for
      select [ID] from @RULESTABLE;

    open RULECURSOR;
    fetch next from RULECURSOR into @RULEID;

    while (@@FETCH_STATUS = 0)
    begin
      /* Save the rule */
      update dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE] set
        [ASKLADDERID] = (case when @ASKLADDERLOADED = 1 then (select T.c.value('(ASKLADDERID)[1]','uniqueidentifier') AS 'ASKLADDERID' from @ASKLADDER.nodes('/ASKLADDER/ITEM') T(c)) else [ASKLADDERID] end),
        [PACKAGEID] = (case when @PACKAGELOADED = 1 then @PACKAGEID else [PACKAGEID] end),
        [RESPONSERATE] = (case when @RESPONSERATELOADED = 1 then @RESPONSERATE else [RESPONSERATE] end),
        [GIFTAMOUNT] = (case when @GIFTAMOUNTLOADED = 1 then @GIFTAMOUNT else [GIFTAMOUNT] end),
        [MAXDAYSCHECKED] = (case when @MAXDAYSLOADED = 1 then @MAXDAYSCHECKED else [MAXDAYSCHECKED] end),
        [MAXDAYS] = (case when @MAXDAYSLOADED = 1 then @MAXDAYS else [MAXDAYS] end),
        [MINQUANTITYCHECKED] = (case when @MINQUANTITYLOADED = 1 then @MINQUANTITYCHECKED else [MINQUANTITYCHECKED] end),
        [MINQUANTITY] = (case when @MINQUANTITYLOADED = 1 then @MINQUANTITY else [MINQUANTITY] end),
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = @CURRENTDATE
      where [ID] = @RULEID;

      fetch next from RULECURSOR into @RULEID;
    end;

    close RULECURSOR;
    deallocate RULECURSOR;
  end try

  begin catch
    exec dbo.USP_RAISE_ERROR;
    return 1;
  end catch

  return 0;