Paging in T-SQL

Question

ALTER PROCEDURE [dbo].[getMessages]
    -- Add the parameters for the stored procedure here
    @lastRow int,
    @sort varchar(9)
AS
BEGIN
    -- Insert statements for procedure here
    DECLARE @StartRow INT,@EndRow INT
    SELECT @StartRow = (@lastRow + 1), @EndRow = (@lastRow + 6)

;WITH cte AS (SELECT ROW_NUMBER() OVER (ORDER BY
        CASE WHEN @sort = 'votes1' THEN m.votes END DESC,
        CASE WHEN @sort = 'votes2' THEN m.votes END ASC
      ) AS rows,
      m.message,
      m.messageId,
      TotalCount = COUNT(m.messageId) OVER ( PARTITION BY NULL)
    FROM
      tblMessages m
    WHERE
      m.deleted != 1
      )
     SELECT * 
     FROM cte WHERE ROWS BETWEEN @StartRow AND @EndRow
    ORDER BY rows
END

So this is my proc that I use for paging so on the front end I can pass in the last row I saw, and then when I click "load more", it starts from the next row and gets the next 6. Well, not I want the PREVIOUS 6, passing in an id, so if you see 6, go to the next six, and then want to see the previous 6 again.

How would I modify this proc to do that?

Answer 1

You're passing in the "last row" but you're not passing in anything that tells you a direction. Instead of passing in last row, why not pass in the page number you want? Page 1 = rows 1-6, page 2 = rows 7-12, etc. Now the procedure doesn't have to remember state, as it only cares which set of rows you want next. If you search for paging stored procedure you will find many examples that do exactly what you want to do.

Answer 2

You don't.

Instead, modify your code. The key in the proc is the @lastrow. When you execute it the first time I'm assuming @lastrow = 0. If they go forward, you are executing it with a value of 6.

To go backward, just pass the current value - 6. For example, if you're on page 20, @lastrow is going to be 114. Subtract 6 in your code and call the proc again.