CREATE PROCEDURE dbo.[Forum_Thread_GetUnread]
    (
      @ModuleID INT,
      @PageSize INT,
      @PageIndex INT,
      @LoggedOnUserID INT
    )
AS
    DECLARE @sql NVARCHAR(2000)    
    DECLARE @PageLowerBound INT
    DECLARE @PageUpperBound INT

    SET @PageLowerBound = @PageSize * @PageIndex
    SET @PageUpperBound = @PageLowerBound + @PageSize + 1

-- Create a temp table to store the select results
    CREATE TABLE #PageIndex
        (
          IndexID INT IDENTITY(1, 1)
                      NOT NULL,
          ThreadID INT
        )

-- INSERT into the temp table
-- Create dynamic SQL to populate temporary table    
    SELECT  @sql = 'INSERT INTO #PageIndex (ThreadID) '
            + 'SELECT T.ThreadID FROm dbo.Forum_Threads T '
            + 'INNER JOIN dbo.Forum_Posts P ON T.LastPostedPostID = P.PostID '
            + 'INNER JOIN dbo.Forum_Forums F ON T.ForumID = F.ForumID '
            + 'INNER JOIN dbo.Forum_Groups G ON F.GroupID = G.GroupID '
            + 'WHERE    ( ( P.CreatedDate > ( SELECT    UT.LastVisitDate '
            + 'FROM    dbo.Forum_UserThreads UT '
            + 'WHERE T.ThreadID = UT.ThreadID ' + 'AND UT.UserID = '
            + CONVERT(VARCHAR, @LoggedOnUserID) + ' ) ) '
            + 'OR ( T.ThreadID NOT IN ( SELECT UT.ThreadID '
            + 'FROM dbo.Forum_UserThreads UT '
            + 'WHERE T.ThreadID = UT.ThreadID ' + 'AND UT.UserID = '
            + CONVERT(VARCHAR, @LoggedOnUserID) + ' ) ) ) '
            + 'AND        ( F.PublicView = 1 OR F.ForumID IN ( '
            + 'SELECT ForumID ' + 'FROM  dbo.Forum_ForumPermission '
            + 'WHERE AllowAccess=1 AND '
            + 'RoleID IN (SELECT RoleID FROM dbo.UserRoles WHERE UserID = '
            + CONVERT(VARCHAR, @LoggedOnUserID) + ' ) '
            + 'OR EXISTS (SELECT TOP 1 1 FROM dbo.Users WHERE UserID = '
            + CONVERT(VARCHAR, @LoggedOnUserID) + ' AND IsSuperUser=1) '
            + ') ) ' + 'AND G.ModuleID = ' + CONVERT(VARCHAR, @ModuleID) + ' '
            + 'ORDER BY F.[SortOrder], T.IsPinned DESC, P.CreatedDate DESC'

    EXEC sp_executesql @sql

    SELECT  [Subject],
            Body,
            CreatedDate,
            UserID AS StartedByUserID,
            T.ThreadID,
            ForumID,
            [Views],
            LastPostedPostID AS LastApprovedPostID,
            ( SELECT    COUNT(ThreadID)
              FROM      dbo.Forum_Posts
              WHERE     ThreadID = T.ThreadID
                        AND PostID <> T.ThreadID
            ) AS Replies,
            IsPinned,
            PinnedDate,
            IsClosed,
            ThreadStatus,
            AnswerPostID,
            AnswerUserID,
            PollID,
            AnswerDate,
            SitemapInclude,
            ISNULL(( SELECT MAX(ThreadID)
                     FROM   dbo.Forum_Threads
                     WHERE  ThreadID < T.ThreadID
                            AND ForumID = T.ForumID
                   ), 0) AS PreviousThreadID,
            ISNULL(( SELECT MIN(ThreadID)
                     FROM   dbo.Forum_Threads
                     WHERE  ThreadID > T.ThreadID
                            AND ForumID = T.ForumID
                   ), 0) AS NextThreadID,
            ISNULL(( SELECT COUNT(ThreadID)
                     FROM   dbo.Forum_ThreadRating TR
                     WHERE  TR.ThreadID = T.ThreadID
                   ), 0) AS RatingCount,
            ISNULL(( SELECT AVG(Rate)
                     FROM   dbo.Forum_ThreadRating TR
                     WHERE  TR.ThreadID = T.ThreadID
                   ), 0) AS Rating,
            ( SELECT    COUNT(ThreadID)
              FROM      #PageIndex
            ) AS TotalRecords,
            CI.CreatedByUserID,
            CI.CreatedOnDate,
            CI.ContentKey,
            CI.Indexed,
            CI.Content,
            CI.ContentItemID,
            CI.LastModifiedByUserID,
            CI.LastModifiedOnDate,
            CI.ModuleID,
            CI.TabID,
            CI.ContentTypeID
    FROM    dbo.Forum_Threads T
            INNER JOIN #PageIndex I ON T.ThreadID = I.ThreadID
            INNER JOIN dbo.Forum_Posts P ON I.ThreadID = P.PostID
            LEFT JOIN dbo.ContentItems CI ON T.ContentItemID = CI.ContentItemID
    WHERE   I.IndexID > @PageLowerBound
            AND I.IndexID < @PageUpperBound
    ORDER BY T.IsPinned DESC,
            P.CreatedDate DESC

GO