USP_FAFNFGCAMPAIGN_DELETE

CREATE procedure dbo.USP_FAFNFGCAMPAIGN_DELETE
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier
)
as 
    set nocount on;

  begin

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

  declare @CURRENTDATE datetime;
  set @CURRENTDATE = getdate();  

--AN NFG can have 1-n levels, each of which need to be deleted before the NFG itself can be  

  declare @LEVELS table(LEVELID uniqueidentifier)

  INSERT INTO @LEVELS(LEVELID)
  SELECT ID FROM FAFNFGCAMPAIGNLEVEL WHERE NFGCAMPAIGNID=@ID

  --clear out any associations between local groups and the campaign levels

  UPDATE dbo.TEAMEXTENSION set NFGCAMPAIGNLEVELID=NULL, CHANGEDBYID=@CHANGEAGENTID, DATECHANGED=@CURRENTDATE
  where NFGCAMPAIGNLEVELID in (select LEVELID from @LEVELS)

  --delete any site display entries for those levels

  DELETE from dbo.FAFNFGCAMPAIGNLEVELDISPLAYSITE where NFGCAMPAIGNLEVELID in (select LEVELID from @LEVELS)

  --delete any friendly url values for those levels

  DELETE from dbo.FAFFRIENDLYURLPARAMS where NFGCAMPAIGNLEVELID in (select LEVELID from @LEVELS)

  --finally, delete the levels themselves

  DELETE from FAFNFGCAMPAIGNLEVEL where NFGCAMPAIGNID=@ID

  --now, on to the actual campaign deletion

  DELETE FROM FAFNFGCAMPAIGNDISPLAYSITE where NFGCAMPAIGNID=@ID

  exec dbo.USP_FAFNFGCAMPAIGN_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID;

    return 0;

end