1

Possible Duplicate:
What is the best way to paginate results in MS SQLServer

I am building stored procedure that should return to me first 15 rows when from database table when it is required.

On user action I need to load next 15 and on each user action load 15 more rows.

This table potently can be very large (over 1.000.000 rows).

I make it this way. I just want to be sure that I am doing this right:

@m_HospitalId int,
@m_Id int
AS
SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY CreatedOnDate ) AS RowNum, *
          FROM      dbo.Items
          WHERE     ItemId >= @m_Id
        ) AS RowConstrainedResult
WHERE   HospitalId = @m_HospitalId AND RowNum > 1 AND RowNum < 15
ORDER BY RowNum

I use ROW_NUMBER() function for the first time and I am not sure if I am doing it right.

Do I always need to pass last ID that I receive to get next 15 items.

In application I handle this when I get collection of 15 items I store 15th item ID in Session and value from session I pass to get next 15 items.

Community
  • 1
  • 1
1110
  • 7,829
  • 55
  • 176
  • 334
  • 1
    Almost certainly wrong - you're assuming that `ItemId` and `CreatedOnDate` are correlated, which won't generally be true. In addition, you're numbering the rows without considering the filter (`HospitalId`). – Damien_The_Unbeliever Mar 12 '12 at 19:30
  • @Omar Yeah, it's a duplicate, but the chosen answer to that question sucks. – Nick Vaccaro Mar 12 '12 at 19:51
  • @Norla - The code in the chosen answer is almost identical to what the OP has. He just needs to read the explanation. – Omar Mar 12 '12 at 19:53
  • @Omar The issue I have with the answer is that it does not guarantee uniqueness between any two pages. Let's say you've got 150 rows for a given CreatedOnDate. If that's the only column in the ORDER BY, you _could_ get the same exact 15 rows for the first 10 pages, or, much more likely, some subset of those 15 back repeatedly. – Nick Vaccaro Mar 12 '12 at 19:59

0 Answers0