USP_SITEPAGES_BULKDELETE

CREATE procedure dbo.USP_SITEPAGES_BULKDELETE(@XML xml)
AS        
begin        
    set nocount on
    declare @SitePageIDS table ([SitePageID] int)

    insert into @SitePageIDS select IDS.ID.value('.', 'int') from @XML.nodes('/SitePages/id') IDS(ID)

    if (EXISTS (SELECT TOP 1 ID FROM sitepages inner join @SitePageIDS SP on sitepages.templatepageid=SP.SitePageID)) begin
    RAISERROR ('Unable to delete the Templates - one or more Template are in use by the Pages.',16,1)
    return 0
    end

    if (EXISTS (SELECT TOP 1 ID FROM CMSSITESETTING inner join @SitePageIDS SP on CAST(CMSSITESETTING.VALUE as int)=SP.SitePageID where ENUMID=2)) begin
    RAISERROR ('One or more Pages are configured as the Home Page in Site Settings.  They may not be deleted.',16,1)
    return 0
    end

    if (EXISTS (SELECT TOP 1 ID FROM CMSSITESETTING inner join @SitePageIDS SP on CAST(CMSSITESETTING.VALUE as int)=SP.SitePageID where ENUMID=3)) begin
    RAISERROR ('One or more Pages are configured as the Login Page in Site Settings.  They may not be deleted.',16,1)
    return 0
    end

    if (EXISTS (SELECT TOP 1 ID FROM CMSSITESETTING inner join @SitePageIDS SP on CAST(CMSSITESETTING.VALUE as int)=SP.SitePageID where ENUMID=4)) begin
    RAISERROR ('One or more Pages are configured as the Authorization Failed Page in Site Settings.  They may not be deleted.',16,1)
    return 0
    end

    if (EXISTS (SELECT TOP 1 ID FROM CMSSITESETTING inner join @SitePageIDS SP on CAST(CMSSITESETTING.VALUE as int)=SP.SitePageID where ENUMID=5)) begin
    RAISERROR ('One or more Pages are configured as the Privacy Page in Site Settings.  They may not be deleted.',16,1)
    return 0
    end

    if (EXISTS (SELECT TOP 1 ID FROM fundraiserpages inner join @SitePageIDS SP on fundraiserpages.sitepageid=SP.SitePageID)) begin
    RAISERROR ('One or more Pages are in use by the Fundraisers.  They may not be deleted.',16,1)
    return 0
    end

    if (EXISTS (SELECT TOP 1 ID FROM chapterpages inner join @SitePageIDS SP on chapterpages.sitepageid=SP.SitePageID)) begin
    RAISERROR ('One or more Pages are in use by one the Chapter Managers.  They may not be deleted.',16,1)
    return 0
    end

    BEGIN TRAN

            delete vanityurl from dbo.vanityurl inner join @SitePageIDS SP on vanityurl.pageid = SP.SitePageID

      -- SHL BBIS User Story 530366; If a page is deleted, its BBSP template settings should also be deleted

      -- Get the currently active environment

      declare @ENVIRONMENTID uniqueidentifier;
      select @ENVIRONMENTID = ID from dbo.CRMBBENVIRONMENT where ISACTIVE = 1;
      delete CMSSitePageBBPayTemplateVersion from dbo.CMSSitePageBBPayTemplateVersion 
      inner join @SitePageIDS SP on CMSSitePageBBPayTemplateVersion.SitePageId = SP.SitePageID and CMSSitePageBBPayTemplateVersion.EnvironmentId = @ENVIRONMENTID;

      delete sitepages from dbo.sitepages inner join @SitePageIDS SP on sitepages.id = SP.SitePageID

      declare SITEPAGESCURSOR cursor local for
      select Guid from dbo.SitePages inner join @SitePageIDS SP on SitePages.ID=SP.SitePageID

      declare @GUID uniqueidentifier

      open SITEPAGESCURSOR
      fetch next from SITEPAGESCURSOR into @GUID

      while @@FETCH_STATUS = 0
      begin
          exec spDelete_ObjectSecurity @Guid
          fetch next from SITEPAGESCURSOR into @Guid
      end

      --When a cursor is used, it should be explicitly closed/deallocated in case of blocking or USP running long

      close SITEPAGESCURSOR;
      deallocate SITEPAGESCURSOR;
    COMMIT TRAN    
end