USP_DATAFORMTEMPLATE_EDIT_MKTSPONSORSHIPMAILINGTEMPLATESEED_2

create procedure dbo.[USP_DATAFORMTEMPLATE_EDIT_MKTSPONSORSHIPMAILINGTEMPLATESEED_2]
(
  @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @SEEDS xml
)
as
  set nocount on;

  declare @CURRENTDATE datetime;
  declare @SEEDID uniqueidentifier;
  declare @SELECTED bit;
  declare @MARKETINGSPONSORSHIPSEEDID uniqueidentifier;

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

    set @CURRENTDATE = getdate();

    declare SEEDCURSOR cursor local fast_forward for
      select 
        [ID], 
        [SELECTED]
      from dbo.[UFN_MKTSPONSORSHIPMAILINGTEMPLATESEED_GETSEEDS_2_FROMITEMLISTXML](@SEEDS);

    open SEEDCURSOR;
    fetch next from SEEDCURSOR into @SEEDID, @SELECTED;

    while (@@FETCH_STATUS = 0)
      begin
        select 
          @MARKETINGSPONSORSHIPSEEDID = [ID] 
        from dbo.[MKTSPONSORSHIPMAILINGTEMPLATESEED] 
        where [SPONSORSHIPMAILINGTEMPLATEID] = @ID 
        and [SEEDID] = @SEEDID;

        if @SELECTED = 0
          begin
            if @MARKETINGSPONSORSHIPSEEDID is not null
              exec dbo.[USP_MKTSPONSORSHIPMAILINGTEMPLATESEED_DELETEBYID_WITHCHANGEAGENTID] @MARKETINGSPONSORSHIPSEEDID, @CHANGEAGENTID;
          end
        else
          begin
            if @MARKETINGSPONSORSHIPSEEDID is null
              insert into dbo.[MKTSPONSORSHIPMAILINGTEMPLATESEED] (
                [ID],
                [SPONSORSHIPMAILINGTEMPLATEID],
                [SEEDID],
                [ADDEDBYID],
                [CHANGEDBYID],
                [DATEADDED],
                [DATECHANGED]
              ) 
              values 
              (
                newid(),
                @ID,
                @SEEDID,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE
              );
          end

        set @MARKETINGSPONSORSHIPSEEDID = null;

        fetch next from SEEDCURSOR into @SEEDID, @SELECTED;
      end;

    close SEEDCURSOR;
    deallocate SEEDCURSOR;
  end try

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

  return 0;