USP_FAFCAMPAIGN_DELETE

CREATE procedure dbo.USP_FAFCAMPAIGN_DELETE
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier
)
as

set nocount on;

begin

  declare @INDEX int = 1,
          @MAXINDEX int,
          @CURRENTID uniqueidentifier = null,
          @CURRENTDATE datetime;
  declare @CAMPAIGNS table(SEQUENCE int IDENTITY(1, 1), ID uniqueidentifier);

  set @CURRENTDATE = getdate();

  insert into @CAMPAIGNS
    select ID from dbo.FAFNFGCAMPAIGN where CAMPAIGNID = @ID

  select @MAXINDEX = MAX(SEQUENCE) from @CAMPAIGNS

  BEGIN TRAN
  begin try

    -- remove FAF NFG from this campaign

      while (@MAXINDEX is not null and @INDEX <= @MAXINDEX)
    begin
      select @CURRENTID = ID from @CAMPAIGNS where SEQUENCE = @INDEX
      if @CURRENTID is not null
        exec dbo.USP_FAFNFGCAMPAIGN_DELETE @CURRENTID, @CHANGEAGENTID;
      set @INDEX = @INDEX + 1
    end

    -- remove event from this campaign

    update dbo.EVENTEXTENSION
    set FAFPROGRAMID = NULL, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTDATE
    where FAFPROGRAMID = @ID

    -- remove event batch from this campaign

    update dbo.FAFEVENTBATCH
    set EVENTPROGRAMID = NULL, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTDATE
    where EVENTPROGRAMID = @ID

    exec dbo.USP_FAFPROGRAM_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID

  end try

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

  COMMIT TRAN;
    return 0;

end