spCloneSite_PageContent

CREATE PROCEDURE [dbo].[spCloneSite_PageContent]
(
    @CopyBatchGuid uniqueidentifier
)
as
begin
    set nocount on;

    begin try

    INSERT INTO dbo.PageContent
    (SitePagesID, SiteContentID, ContentOrder, RequiresSSL, PaneName)
    SELECT
    SitePages.ID,
    ISNULL(SC.ID,PageContent.SiteContentID), -- try to fall back to the part that is on the page that was copied

    PageContent.ContentOrder,
    PageContent.RequiresSSL,
    PageContent.PaneName
    FROM dbo.PageContent
    INNER JOIN dbo.SitePages ON SitePages.CopySourceID = PageContent.SitePagesID AND SitePages.CopyBatchGuid = @CopyBatchGuid -- get the copied page

    LEFT JOIN dbo.SiteContent SC ON SC.CopySourceID = PageContent.SiteContentID AND SC.CopyBatchGuid = @CopyBatchGuid -- get the copied part if it exists

    LEFT JOIN dbo.SiteContent SC_PAGE on PageContent.SiteContentID = SC_PAGE.ID -- need to verify the current page part actually exists and was not just deleted while sill being on the page (this is prevented in the application now.)

    WHERE (SC.ID is not null or (SC.ID is null and SC_PAGE.ID is not null)) and (SC.ContentTypesID NOT IN ( 69 ) -- Fundraiser part shouldn't have a new page created on the new Site.

            or SC.ContentTypesID is null)

    end try
    begin catch
          exec dbo.USP_RAISE_ERROR;
    end catch

end