spLoadRecord_SitePages

CREATE procedure [dbo].[spLoadRecord_SitePages]
(
    @PKID int,
    @Status int = 2,
    @DesignMode bit = 0
)
as 
begin

    /* first result set - page info */
    select 
    ID, ClientSitesID, 
    PageName,
    DisplayName, 
    IsAdmin, 
    BaseURL, 
    [ExpireDate], 
    PublishDate, 
    [Guid], 
    OwnerID, 
    Deleted,
    TemplatePageID,
    IsTemplatePage,
    Category,
    LayoutName,
    TemplatePageName,
    LayoutID,
    TemplateLayoutName,
    TemplateLayoutID,
    TemplateGUID,
    FolderId,
    PageLayoutStyleSheet,
    TemplateLayoutStyleSheet,
    StyleSheetID,
    [Description],
    PageKeywords,
    TemplateKeywords,
    UseTemplateKeywords,
    TemplateDescription,
    [UpdateDate],
    IsPageModel,
    FromModelId,
    Locked,
    IsThemed,
    ThemeLayoutNumber,
    URL    
    from dbo.vwSitePages
    where ID = @PKID

    declare @TemplatePageId int    
    select @TemplatePageId = TemplatePageID from SitePages where ID = @PKID


    declare @PC table (
    [ID] int, [Title] nvarchar(256), [Description] nvarchar(256), [XMLData] ntext, [ContentExpireDate] datetime, [ContentPublishDate] datetime, [CacheTime] int, [Guid] uniqueidentifier,
    [OwnerID] int, [Approved] bit, [ContentTypesID] int, [ClientSitesID] int, [IsTimePublished] bit, [LanguageTarget] nvarchar(256), [WorkflowID] int, [TestMode] bit, [FromModelId] int, 
    [FolderID] int, [TypeName] nvarchar(128), [DesktopSrc] nvarchar(256), [EditControl] nvarchar(256), [ClassName] nvarchar(256), [Static] bit, [MustUseSSL] bit, [Custom] bit, [CustomPD] bit,
    [UniqueContent] bit, [CanCopy] bit, [RealmTypeId] int, [Owner] nvarchar(50), [CreateDate] datetime, [Discoverable] bit, [DiscoverableTitle] nvarchar(255), [DiscoverableSummary] nvarchar(4000),
    [UpdateDate] datetime, [SiteContentVersionID] int, [Status] int, [StatusUpdatedOn] datetime, [StatusUpdatedBy] int, [StatusUpdatedByName] nvarchar(50), [Comments] nvarchar(256),
    [SupportsLimitedConnectivity] bit, [Flag] nvarchar(50), [CanUpdate] bit, [CanShare] bit, [ShareInSiteCopy] bit, [CanCreateLocalCopy] bit, [ChangedByCMSUserID] int, [BBSPEnabled] bit, [ContentOrder] int,
    [PaneName] nvarchar(256), [PageContentID] int, [RequiresSSL] bit, [PageID] int, [PageName] nvarchar(255), [IsAdmin] bit, [BaseURL] nvarchar(50), [ExpireDate] datetime, [PublishDate] datetime,
    [TemplateID] int, [PageGuid] uniqueidentifier, [PageOwnerID] int, [IsTemplatePage] bit, [DisplayName] nvarchar(255))

    insert into @PC
    select *
    from dbo.vwPageContent 
    where PageID IN(@PKID,@TemplatePageID)     

    /* second result set - get page contents */
    if @DesignMode = 0
        select *
        from @PC
        where (([WorkflowID] is not null 
            and ((@Status not in (0,1) and [Status] = @Status)
            or (@Status in (0,1) and [Status] in (0,1,2))))
            /* if no workflows are set for the content type, show pending (latest) version */
            or ([WorkflowID] is null and [Status]=2))
        order by ID

    else
        -- CR303213-062308 ChY 6-24-08:                 
        select * from @PC PC
        INNER JOIN (select ID as DUPID, Max(ISNULL(SiteContentVersionID,0)) AS MAXID 
                    from dbo.vwPageContent WHERE [status] <> 4 GROUP By ID) T1

        ON PC.ID=T1.DUPID and ISNULL(PC.SiteContentVersionID,0)=T1.MAXID
        where (([WorkflowID] is not null 
            and ((@Status not in (0,1) and [Status] = @Status)
            or (@Status in (0,1) AND [Status] in (0,1,2))))
        /* if no workflows are set for the content type, show pending (latest) version */
        or ([WorkflowID] is null and [Status]=2))
        order by ID

    /* third / forth result set - get page contents */
    if @TemplatePageId <= 0
    begin
        exec dbo.USP_DATALIST_CMSTEMPLATELAYOUTS @PKID;
    end
    else
    begin
        /*To ensure we always have the same number of result sets, return one null sets for real pages (non-templates)*/
        select 1 where 1=0
    end                
end