USP_OPPORTUNITY_CLEARANDADDCAMPAIGNS

CREATE procedure dbo.USP_OPPORTUNITY_CLEARANDADDCAMPAIGNS
(
  @OPPORTUNITYID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier,
  @CHANGEDATE datetime
)
as
  set nocount on;

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

  if @CHANGEDATE is null
    set @CHANGEDATE = getdate();

  begin try
    declare @contextCache varbinary(128);

    --cache current context information

    set @contextCache = CONTEXT_INFO();

    --set CONTEXT_INFO to @CHANGEAGENTID

    set CONTEXT_INFO @CHANGEAGENTID;

    delete from dbo.OPPORTUNITYDESIGNATIONCAMPAIGN 
    where OPPORTUNITYDESIGNATIONID in (
      select ID
      from dbo.OPPORTUNITYDESIGNATION 
      where OPPORTUNITYID =  @OPPORTUNITYID
    );

    --reset CONTEXT_INFO to previous value

    if not @contextCache is null
      set CONTEXT_INFO @contextCache;

    insert into dbo.OPPORTUNITYDESIGNATIONCAMPAIGN (OPPORTUNITYDESIGNATIONID, CAMPAIGNID, CAMPAIGNSUBPRIORITYID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
      select distinct
        OPPORTUNITYDESIGNATION.ID,
        DESIGNATIONCAMPAIGN.CAMPAIGNID,
        DESIGNATIONCAMPAIGN.CAMPAIGNSUBPRIORITYID,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CHANGEDATE,
        @CHANGEDATE
      from dbo.OPPORTUNITYDESIGNATION
      inner join dbo.OPPORTUNITY on OPPORTUNITY.ID = OPPORTUNITYDESIGNATION.OPPORTUNITYID
      inner join dbo.DESIGNATIONCAMPAIGN on DESIGNATIONCAMPAIGN.DESIGNATIONID = OPPORTUNITYDESIGNATION.DESIGNATIONID
          --The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...

          and (DESIGNATIONCAMPAIGN.DATEFROM is null or OPPORTUNITY.EXPECTEDASKDATE is null or cast(DESIGNATIONCAMPAIGN.DATEFROM as date) <= OPPORTUNITY.EXPECTEDASKDATE)
          --The "GETLATESTTIME" date function has been inlined here for performance (the part with "dateadd(ms, -003...")...

          and (DESIGNATIONCAMPAIGN.DATETO is null or OPPORTUNITY.EXPECTEDASKDATE is null or dateadd(ms, -003, dateadd(d, 1, cast(cast(DESIGNATIONCAMPAIGN.DATETO as date) as datetime))) >= OPPORTUNITY.EXPECTEDASKDATE)
      where OPPORTUNITYDESIGNATION.OPPORTUNITYID = @OPPORTUNITYID;
  end try

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

  return 0;