USP_MKTSPONSORSHIPMAILINGTEMPLATEKPI_CLEARFIELDS

CREATE procedure dbo.[USP_MKTSPONSORSHIPMAILINGTEMPLATEKPI_CLEARFIELDS]
(
  @SPONSORSHIPMAILINGTEMPLATEID uniqueidentifier,
  @CLEARDEFAULTS bit,
  @CHANGEAGENTID uniqueidentifier = null
)
as
  set nocount on;

  declare @CURRENTDATE datetime;
  declare @ID uniqueidentifier;

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

    set @CURRENTDATE = getdate();

    --Deleting by cursor so the audit tables show correctly...

    declare KPICURSOR cursor local fast_forward for
      select [ID]
      from dbo.[MKTSPONSORSHIPMAILINGTEMPLATEKPI]
      where [SPONSORSHIPMAILINGTEMPLATEID] = @SPONSORSHIPMAILINGTEMPLATEID;

    open KPICURSOR;
    fetch next from KPICURSOR into @ID;

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

    close KPICURSOR;
    deallocate KPICURSOR;

    --Reset all defaults if needed...

    if @CLEARDEFAULTS = 1
      begin
        declare DEFAULTCURSOR cursor local fast_forward for
          select [ID]
          from dbo.[MKTSEGMENTATIONACTIVATEDEFAULTKPI]
          where [MAILINGTYPECODE] = 3;

        open DEFAULTCURSOR;
        fetch next from DEFAULTCURSOR into @ID;

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

        close DEFAULTCURSOR;
        deallocate DEFAULTCURSOR;
      end
  end try

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

  return 0;