CREATE PROCEDURE dbo.[Forum_Thread_GetAll]
    (
      @ModuleID INT,
      @ForumID INT,
      @PageSize INT,
      @PageIndex INT,
      @Filter NVARCHAR(500),
      @PortalID 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 FFP ON T.ThreadID = FFP.PostID'
            + ' INNER JOIN dbo.Forum_Posts FP ON T.LastPostedPostID = FP.PostID '
            + ' WHERE FP.IsApproved = 1 AND ForumID = '
            + CONVERT(VARCHAR, @ForumID) + @Filter
            + ' ORDER BY T.IsPinned DESC, FP.CreatedDate DESC'
    EXEC sp_executesql @sql
    
    SELECT  FFP.[Subject],
            FLP.Body,
            FLP.CreatedDate,
            FFP.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
                    AND IsApproved = 1
            ) AS Replies,
            IsPinned,
            PinnedDate,
            FFP.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 FFP ON I.ThreadID = FFP.PostID
            INNER JOIN dbo.Forum_Posts FLP ON T.LastPostedPostID = FLP.PostID
            LEFT OUTER JOIN dbo.ContentItems CI ON T.ContentItemID = CI.ContentItemID
    WHERE   I.IndexID > @PageLowerBound
            AND I.IndexID < @PageUpperBound
    ORDER BY T.IsPinned DESC,
            FLP.CreatedDate DESC

GO