spUserPagesForUserId

CREATE PROCEDURE [dbo].[spUserPagesForUserId] (@ClientUserID INT, @RealmType INT = -1) 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(100)
    )

    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.ClientUserId = @ClientUserID
        OR upe.ClientUsersID = @ClientUserID)
        AND (@RealmType = -1 OR upt.RealmType = @RealmType)
    ORDER BY DisplayName

    IF @RealmType = 7 --TeamPages

    BEGIN
        UPDATE @Pages
            --Sport - Division Gender

        SET RealmContextName = cte1.Description +
            (Case
                WHEN ((LEN(COALESCE(cte2.description, '')) + LEN(COALESCE(cte3.Description, ''))) > 0)
                    THEN ' -' + COALESCE(' ' + cte2.Description, '') + COALESCE(' ' + cte3.Description, '')
                ELSE
                    ''
             END)
        FROM @Pages p 
            LEFT JOIN dbo.ATHLETICS_TEAMS at 
                ON p.RealmContextId = at.ID
            LEFT JOIN dbo.SiteCodeTableEntries cte1
                ON at.Sport = cte1.EntryGUID
            LEFT JOIN dbo.SiteCodeTableEntries cte2
                ON at.Division = cte2.EntryGUID
            LEFT JOIN dbo.SiteCodeTableEntries cte3
                ON at.Gender = cte3.EntryGUID
    END

    SELECT * 
    FROM @Pages
    ORDER BY RealmContextName, RealmContextID, DisplayName