CREATE PROCEDURE dbo.[Forum_Bookmark_Threads_Get]
    (
      @UserID INT,
      @ModuleID INT,
      @PageSize INT,
      @PageIndex INT
    )
AS
    DECLARE @RowStart INT
    DECLARE @RowEnd INT
                
    SET @RowStart = @PageSize * @PageIndex + 1 ;
    SET @RowEnd = @RowStart + @PageSize - 1 ;

    SELECT  *
    FROM    ( SELECT    BT.ThreadID,
                        BT.UserID,
                        BT.ModuleID,
                        ( SELECT TOP 1
                                    Subject
                          FROM      dbo.Forum_Posts
                          WHERE     ThreadID = BT.ThreadID
                          ORDER BY  PostID
                        ) AS 'Subject',
                        T.ForumID,
                        T.LastPostedPostID AS 'MostRecentPostID',
                        T.Replies + 1 AS TotalPosts,
                        ROW_NUMBER() OVER ( ORDER BY P.CreatedDate DESC ) AS RowNumber,
                        ( SELECT    COUNT(T.ThreadID)
                          FROM      dbo.Forum_Bookmark_Threads BT
                                    INNER JOIN dbo.Forum_Threads T ON BT.ThreadID = T.ThreadID
                                    INNER JOIN dbo.Forum_Posts P ON T.LastPostedPostID = P.PostID
                          WHERE     BT.UserID = @UserID
                                    AND BT.ModuleID = @ModuleID
                        ) AS TotalRecords
              FROM      dbo.Forum_Bookmark_Threads BT
                        INNER JOIN dbo.Forum_Threads T ON BT.ThreadID = T.ThreadID
                        INNER JOIN dbo.Forum_Posts P ON T.LastPostedPostID = P.PostID
              WHERE     BT.UserID = @UserID
                        AND BT.ModuleID = @ModuleID
            ) AS TrackingInfo
    WHERE   RowNumber >= @RowStart
            AND RowNumber <= @RowEnd

GO