CREATE PROCEDURE dbo.[Forum_Search]
    (
      @Filter NVARCHAR(500),
      @PageIndex INT,
      @PageSize INT,
      @UserID INT,
      @ModuleID INT,
      @FromDate DATETIME,
      @ToDate DATETIME,
      @ThreadStatusID INT
    )
AS -- Create a temp table to store the select results
    CREATE TABLE #PageIndex
        (
          IndexID INT IDENTITY(1, 1)
                      NOT NULL,
          CreatedDate DATETIME NULL,
          ThreadID INT,
          PostID INT
        )
    -- Create dynamic SQL to populate temporary table
    DECLARE @sql NVARCHAR(2000)
    SELECT  @sql = 'INSERT INTO #PageIndex(PostID, ThreadID, CreatedDate) SELECT DISTINCT TOP 500 (PostID), (ThreadID), '
            + '(SELECT FP.CreatedDate FROM dbo.Forum_Threads FT INNER JOIN dbo.Forum_Posts FP '
            + 'ON FT.LastPostedPostID = FP.PostID '
            + 'INNER JOIN dbo.Forum_Posts FFP ON FT.ThreadID = FFP.PostID '
            + 'WHERE FT.ThreadID = PS.ThreadID) As LastPostDate '
            + 'FROM dbo.Forum_vw_PostSearch PS ' + 'WHERE [CreatedDate] >  '''
            + CONVERT(VARCHAR, @FromDate) + ''' ' + 'AND [CreatedDate] < '''
            + CONVERT(VARCHAR, @ToDate) + ''' ' + 'AND IsApproved = 1 '
            + 'AND IsActive = 1 ' + 'AND ModuleID = '
            + CONVERT(VARCHAR, @ModuleID)
            + 'AND ( (EnableForumsThreadStatus = 0) '
            + 'OR (EnableForumsThreadStatus = 1 AND ThreadStatus = '
            + CONVERT(VARCHAR, @ThreadStatusID) + ') '
            + 'OR (EnableForumsThreadStatus = 1 AND -1 = '
            + CONVERT(VARCHAR, @ThreadStatusID) + ') ) '
            + 'AND ( PublicView = 1 OR ' + ' ForumID IN ( '
            + ' SELECT ForumID ' + ' FROM  dbo.Forum_ForumPermission '
            + ' WHERE AllowAccess=1 AND '
            + '(RoleID IN (SELECT RoleID FROM dbo.UserRoles WHERE UserID = '
            + CONVERT(VARCHAR, @UserID) + ' )' + ' OR EXISTS '
            + '(SELECT TOP 1 1 FROM dbo.Users WHERE UserID = '
            + CONVERT(VARCHAR, @UserID) + ' AND IsSuperUser=1)))) ' + @Filter
            + 'GROUP BY PostID, ThreadID ORDER BY LastPostDate DESC'
    EXEC sp_executesql @sql
    -- All of the rows are inserted into the table - now select the correct subset
    DECLARE @PageLowerBound INT
    DECLARE @PageUpperBound INT
    DECLARE @RecordCount INT
    -- Set the page bounds
    SET @PageLowerBound = @PageSize * @PageIndex
    SET @PageUpperBound = @PageLowerBound + @PageSize + 1
    
    -- Select the data out of the temporary table
    SELECT  I.PostID,
            I.ThreadID,
            FP.[Subject],
            FP.Body,
            FP.CreatedDate,
            FP.UserId,
            FP.ParentPostID,
            FP.RemoteAddr,
            FP.UpdatedDate,
            FP.UpdatedByUser,
            FP.IsApproved,
            FP.IsLocked,
            FP.IsClosed,
            FP.PostReported,
            FP.Addressed,
            FP.ParseInfo,
            SitemapInclude,
            @ModuleID AS ModuleID,
            ( SELECT    COUNT(*)
              FROM      #PageIndex
            ) AS TotalRecords
    FROM    dbo.Forum_Threads T
            INNER JOIN #PageIndex I ON T.ThreadID = I.ThreadID
            INNER JOIN dbo.Forum_Posts FP ON I.PostID = FP.PostID
    WHERE   I.IndexID > @PageLowerBound
            AND I.IndexID < @PageUpperBound
    ORDER BY FP.CreatedDate DESC

GO