CREATE PROCEDURE dbo.[Forum_Tracking_ThreadGet]
    (
      @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    TT.ThreadID,
                        TT.ForumID,
                        TT.UserID,
                        TT.ModuleID,
                        ( SELECT TOP 1
                                    [Subject]
                          FROM      dbo.Forum_Posts
                          WHERE     ThreadID = TT.ThreadID
                                    AND IsApproved = 1
                          ORDER BY  PostID
                        ) AS 'Subject',
                        T.LastPostedPostID AS 'MostRecentPostID',
                        T.Replies + 1 AS TotalPosts,
                        ROW_NUMBER() OVER ( ORDER BY P.CreatedDate DESC ) AS RowNumber,
                        ( SELECT    COUNT(ThreadID)
                          FROM      dbo.Forum_TrackedThreads TT
                          WHERE     TT.UserID = @UserID
                                    AND TT.ModuleID = @ModuleID
                        ) AS TotalRecords
              FROM      dbo.Forum_TrackedThreads TT
                        INNER JOIN dbo.Forum_Threads T ON TT.ThreadID = T.ThreadID
                        INNER JOIN dbo.Forum_Posts P ON T.LastPostedPostID = P.PostID
              WHERE     TT.UserID = @UserID
                        AND TT.ModuleID = @ModuleID
            ) AS TrackingInfo
    WHERE   RowNumber >= @RowStart
            AND RowNumber <= @RowEnd

GO