spGetUsersAthleticTeamPages

                CREATE PROCEDURE [dbo].[spGetUsersAthleticTeamPages] (@EEID INT, @Season UNIQUEIDENTIFIER) AS
                SET NOCOUNT ON

                    DECLARE @Pages TABLE (
                        RealmContextID INT,
                        RealmContextName NVARCHAR(772) --<code table entry> - <code table entry> <code table entry>
                    )

                    --If record is a player, get his teams
                    INSERT INTO @Pages (RealmContextID, RealmContextName)
                    SELECT ar.TeamID,
                        --Sport - Division Gender
                            (Case
                                WHEN ((LEN(COALESCE(cte2.description, '')) + LEN(COALESCE(cte3.Description, ''))) > 0)
                                    THEN cte1.Description + ' -' + COALESCE(' ' + cte2.Description, '') + COALESCE(' ' + cte3.Description, '')
                                ELSE
                                    cte1.Description
                             END)
                    FROM dbo.ATHLETICS_ROSTERS ar
                        INNER JOIN dbo.ATHLETICS_TEAMS at 
                            ON ar.TeamID = 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
                    WHERE ar.PlayerEEID = @EEID
                        AND at.Season = @Season

                    --If record is a coach, get his teams
                    INSERT INTO @Pages (RealmContextID, RealmContextName)
                    SELECT ac.TeamID,
                        --Sport - Division Gender
                            (Case
                                WHEN ((LEN(COALESCE(cte2.description, '')) + LEN(COALESCE(cte3.Description, ''))) > 0)
                                    THEN cte1.Description + ' -' + COALESCE(' ' + cte2.Description, '') + COALESCE(' ' + cte3.Description, '')
                                ELSE
                                    cte1.Description
                             END)
                    FROM dbo.ATHLETICS_EECOACHES ac
                        INNER JOIN dbo.ATHLETICS_TEAMS at
                            ON ac.TeamID = 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
                    WHERE ac.CoachEEID = @EEID
                        AND at.Season = @Season

                    select 
                        up.ID,
                        up.UserPageTemplateID,
                        up.ClientUserID,
                        up.DisplayName,
                        up.IsPublished,
                        p.RealmContextID,
                        upt.RealmType,
                        upt.UserPageManagerID,
                        upt.SitePageID,
                        p.RealmContextName
                    FROM @Pages p
                        INNER JOIN UserPages up
                            ON up.RealmContextId = p.RealmContextID
                        INNER JOIN UserPageManagerPageTemplates upt 
                            ON up.UserPageTemplateId = upt.ID
                    WHERE up.IsPublished = 1
                        AND upt.RealmType = 7 --Core.Data.ERealmTypes.AthleticTeam