USP_MKTSEGMENTATION_SWAPSEGMENTS

CREATE procedure dbo.USP_MKTSEGMENTATION_SWAPSEGMENTS
(
  @TOPSEGMENTID uniqueidentifier,
  @BOTTOMSEGMENTID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null
)
as
  set nocount on;

  declare @CURRENTDATE datetime;

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

  set @CURRENTDATE = getdate();

  /* Swap the sequences */
  update dbo.[MKTSEGMENTATIONSEGMENT]
  set [SEQUENCE] = [SEQUENCE] + 1, [CHANGEDBYID] = @CHANGEAGENTID, [DATECHANGED] = @CURRENTDATE
  where [ID] = @TOPSEGMENTID;

  update dbo.[MKTSEGMENTATIONSEGMENT]
  set [SEQUENCE] = [SEQUENCE] - 1, [CHANGEDBYID] = @CHANGEAGENTID, [DATECHANGED] = @CURRENTDATE
  where [ID] = @BOTTOMSEGMENTID;

  /* Clear the cache for the segments and all segments after them with the same record type. */
  /* Not valid for vendor managed segments, so skip. */
  if exists(select 1 from dbo.[MKTSEGMENTATIONSEGMENT] where [ID] = @TOPSEGMENTID and dbo.[UFN_MKTSEGMENT_ISVENDORMANAGEDLIST]([SEGMENTID]) = 0)
    exec dbo.[USP_MKTSEGMENTATIONSEGMENT_CLEARCACHE] @TOPSEGMENTID, 1, 1;
  if exists(select 1 from dbo.[MKTSEGMENTATIONSEGMENT] where [ID] = @BOTTOMSEGMENTID and dbo.[UFN_MKTSEGMENT_ISVENDORMANAGEDLIST]([SEGMENTID]) = 0)
    exec dbo.[USP_MKTSEGMENTATIONSEGMENT_CLEARCACHE] @BOTTOMSEGMENTID, 1, 1;

  return 0;