spUserPagesForRealmContext

CREATE PROCEDURE dbo.spUserPagesForRealmContext(@RealmTypeId int, @RealmContextId int) AS
SET NOCOUNT ON

    DECLARE @Pages TABLE (
        ID INT,
        UserPageTemplateID INT,
        ClientUserID INT,
        DisplayName NVARCHAR(100),
        IsPublished BIT,
        RealmContextID INT,
        RealmType INT,
        UserPageManagerID INT,
        SitePageID INT,
        RealmContextName NVARCHAR(772) --<code table entry> - <code table entry> <code table entry>

    )

    INSERT INTO @Pages
    SELECT DISTINCT
        up.ID, 
        up.UserPageTemplateId, 
        up.ClientUserId, 
        up.DisplayName, 
        up.IsPublished,
        up.RealmContextId,
        upt.RealmType,
        upt.UserPageManagerID,
        upt.SitePageID,
        ''
    FROM UserPages up 
        inner join UserPageManagerPageTemplates upt on up.UserPageTemplateId=upt.ID
        left join UserPagesEditors upe on up.ID = upe.UserPagesID
    WHERE up.RealmContextId = @RealmContextId
        AND upt.RealmType = @RealmTypeId
    ORDER BY DisplayName

    SELECT * 
    FROM @Pages
    ORDER BY RealmContextName, DisplayName