USP_MKTSPONSORSHIPMAILINGTEMPLATEKPI_SAVEFIELD

CREATE procedure dbo.[USP_MKTSPONSORSHIPMAILINGTEMPLATEKPI_SAVEFIELD]
(
  @SPONSORSHIPMAILINGTEMPLATEID uniqueidentifier,
  @ACTIVATIONKPIS xml,
  @DEFAULT bit,
  @CHANGEAGENTID uniqueidentifier = null
)
as
  set nocount on;

  declare @KPIS table ([KPICATALOGID] uniqueidentifier);
  declare @ID uniqueidentifier;
  declare @CURRENTDATE datetime;

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

    set @CURRENTDATE = getdate();

    insert into @KPIS
    select
      T.c.value('(KPICATALOGID)[1]', 'uniqueidentifier') as [KPICATALOGID]
    from @ACTIVATIONKPIS.nodes('/ACTIVATIONKPIS/ITEM') T(c);

    -- insert selected KPIs that aren't already selected

    insert into dbo.[MKTSPONSORSHIPMAILINGTEMPLATEKPI]
    (
      [ID],
      [SPONSORSHIPMAILINGTEMPLATEID],
      [KPICATALOGID],
      [ADDEDBYID],
      [DATEADDED],
      [CHANGEDBYID],
      [DATECHANGED]
    )
    select
      newid(),
      @SPONSORSHIPMAILINGTEMPLATEID,
      [I].[KPICATALOGID],
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CHANGEAGENTID,
      @CURRENTDATE
    from @KPIS as [I]
    where not exists (select top 1 1 from dbo.[MKTSPONSORSHIPMAILINGTEMPLATEKPI] as [K]
                      where [K].[SPONSORSHIPMAILINGTEMPLATEID] = @SPONSORSHIPMAILINGTEMPLATEID
                      and [K].[KPICATALOGID] = [I].[KPICATALOGID]);

    -- delete KPIs that are no longer selected

    declare DELETECURSOR cursor local fast_forward for
      select [K].[ID]
      from dbo.[MKTSPONSORSHIPMAILINGTEMPLATEKPI] as [K]
      where [K].[SPONSORSHIPMAILINGTEMPLATEID] = @SPONSORSHIPMAILINGTEMPLATEID
      and not exists (select top 1 1 from @KPIS as [D]
                      where [K].[KPICATALOGID] = [D].[KPICATALOGID]);


    open DELETECURSOR;
    fetch next from DELETECURSOR into @ID;

    while (@@FETCH_STATUS = 0)
      begin
        exec dbo.[USP_MKTSPONSORSHIPMAILINGTEMPLATEKPI_DELETEBYID_WITHCHANGEAGENTID] @ID, @CHANGEAGENTID;
        fetch next from DELETECURSOR into @ID;
      end

    close DELETECURSOR;
    deallocate DELETECURSOR;

    if @DEFAULT = 1
      begin
        -- insert selected KPIs that aren't already defaults

        insert into dbo.[MKTSEGMENTATIONACTIVATEDEFAULTKPI]
        (
          [ID],
          [MAILINGTYPECODE],
          [KPICATALOGID],
          [ADDEDBYID],
          [DATEADDED],
          [CHANGEDBYID],
          [DATECHANGED]
        )
        select
          newid(),
          3,
          [I].[KPICATALOGID],
          @CHANGEAGENTID,
          @CURRENTDATE,
          @CHANGEAGENTID,
          @CURRENTDATE
        from @KPIS as [I]
        where not exists (select top 1 1 from dbo.[MKTSEGMENTATIONACTIVATEDEFAULTKPI] as [K]
                          where [K].[MAILINGTYPECODE] = 3
                          and [K].[KPICATALOGID] = [I].[KPICATALOGID]);

        -- delete KPIs that are no longer defaults

        declare DELETECURSOR cursor local fast_forward for
          select [K].[ID]
          from dbo.[MKTSEGMENTATIONACTIVATEDEFAULTKPI] as [K]
          where [K].[MAILINGTYPECODE] = 3
          and not exists (select top 1 1 from @KPIS as [D]
                          where [K].[KPICATALOGID] = [D].[KPICATALOGID]);

        open DELETECURSOR;
        fetch next from DELETECURSOR into @ID;

        while (@@FETCH_STATUS = 0)
          begin
            exec dbo.[USP_MKTSEGMENTATIONACTIVATEDEFAULTKPI_DELETEBYID_WITHCHANGEAGENTID] @ID, @CHANGEAGENTID;
            fetch next from DELETECURSOR into @ID;
          end

        close DELETECURSOR;
        deallocate DELETECURSOR;
      end
  end try

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

  return 0;