UFN_MKTSEGMENTATION_COMPARECOMMPREFS

Compares communication preferences settings, and returns true if they're different and false if not.

Return

Return Type
bit

Parameters

Parameter	Parameter Type	Mode
@EXCLUSIONDATETYPECODE1	tinyint	IN
@EXCLUSIONASOFDATE1	datetime	IN
@EXCLUDEDECEASED1	bit	IN
@EXCLUDEINACTIVE1	bit	IN
@EXCLUSIONS1	xml	IN
@EXCLUSIONDATETYPECODE2	tinyint	IN
@EXCLUSIONASOFDATE2	datetime	IN
@EXCLUDEDECEASED2	bit	IN
@EXCLUDEINACTIVE2	bit	IN
@EXCLUSIONS2	xml	IN

Definition

Copy



CREATE function dbo.[UFN_MKTSEGMENTATION_COMPARECOMMPREFS]
(
  @EXCLUSIONDATETYPECODE1 tinyint,
  @EXCLUSIONASOFDATE1 datetime,
  @EXCLUDEDECEASED1 bit, 
  @EXCLUDEINACTIVE1 bit, 
  @EXCLUSIONS1 xml,
  @EXCLUSIONDATETYPECODE2 tinyint,
  @EXCLUSIONASOFDATE2 datetime,
  @EXCLUDEDECEASED2 bit,
  @EXCLUDEINACTIVE2 bit,
  @EXCLUSIONS2 xml
)
returns bit
as
  begin
    declare @DIFFERENT bit;
    set @DIFFERENT = 0;

    if @EXCLUSIONDATETYPECODE1 is not null and (@EXCLUSIONDATETYPECODE1 = 0 or (@EXCLUSIONDATETYPECODE1 = 1 and @EXCLUSIONASOFDATE1 is not null)) and @EXCLUDEDECEASED1 is not null and @EXCLUDEINACTIVE1 is not null
      if @EXCLUSIONDATETYPECODE1 <> @EXCLUSIONDATETYPECODE2 or (@EXCLUSIONDATETYPECODE1 = 1 and @EXCLUSIONASOFDATE1 <> @EXCLUSIONASOFDATE2) or @EXCLUDEDECEASED1 <> @EXCLUDEDECEASED2 or @EXCLUDEINACTIVE1 <> @EXCLUDEINACTIVE2
        set @DIFFERENT = 1;
      else
        begin
          -- compare the old and new @EXCLUSIONS xml to see if the user changed anything...

          declare @E1 table([ID] uniqueidentifier, [SOLICITCODEID] uniqueidentifier not null);
          declare @E2 table([ID] uniqueidentifier, [SOLICITCODEID] uniqueidentifier not null);
          declare @COUNT1 int;
          declare @COUNT2 int;

          insert into @E1 ([ID], [SOLICITCODEID])
            select [ID], [SOLICITCODEID]
            from dbo.[UFN_BUSINESSPROCESSCOMMPREF_GETEXCLUSIONS_FROMITEMLISTXML](@EXCLUSIONS1);

          insert into @E2 ([ID], [SOLICITCODEID])
            select [ID], [SOLICITCODEID]
            from dbo.[UFN_BUSINESSPROCESSCOMMPREF_GETEXCLUSIONS_FROMITEMLISTXML](@EXCLUSIONS2);

          select @COUNT1 = count(1) from @E1;
          select @COUNT2 = count(1) from @E2;

          if @COUNT1 <> @COUNT2
            set @DIFFERENT = 1;
          else
            begin
              select @COUNT2 = count(1)
              from @E1 as [E1]
              inner join @E2 as [E2] on [E2].[SOLICITCODEID] = [E1].[SOLICITCODEID];

              if @COUNT1 <> @COUNT2
                set @DIFFERENT = 1;
              else
                set @DIFFERENT = 0;
            end
        end  

    return @DIFFERENT;
  end;