Paging a database using a stored procedure

Question

I want the stored procedure to accept x the number of data lines for the page y-number. for instance

I have 20 datarow my page size is 2 if I select page 2 I get a data row 17,18

I can use the top 200 and use the order to select the first and last datarow, but how do I get the pages between them.

@PageNumber INT As BEGIN SELECT COUNT(rate.RateID)/200 FROM dbo.Rate where dbo.Rate.Hourly =0 DECLARE @LastIndex INT SET @LastIndex= (SELECT TOP 1 rate.RateID FROM dbo.Rate where dbo.Rate.Hourly =0 ORDER BY rate.RateID ASC) Select TOP 200 [RateID], [PairID], [Open], [Close], [High], [Low], [Difference], [Average], [Percentage], [InfoDate], [Hourly], [CaptureDateTime] From Rate WHERE Hourly =0 AND RateID >=(@LastIndex+(200* @PageNumber)) ORDER BY [RateID] ASC 

The end is what I have now, but it is not working properly

Answer 1

Well, since you did not specify which RDBMS you are using, I can give you a solution that is valid for at least SQL Server 2005 +.

 DECLARE @PageNumber INT, @PageSize INT SET @PageNumber = 3 SET @PageSize = 5; WITH CTE AS ( SELECT *, ROW_NUMBER() OVER(ORDER BY RateID) Corr FROM Rate WHERE Hourly = 0 ) SELECT * FROM CTE WHERE Corr BETWEEN @PageNumber*@PageSize AND @PageNumber*@ PageSize+@PageSize-1 

Also, you should be aware that in the next version of SQL Server ("Denali") this will be much easier with some changes to the TOP clause.

Answer 2

 Select * from( SELECT (ROW_NUMBER()OVER (ORDER BY InfoDate ASC)) AS RowNo, [RateID], [PairID], [Open], [Close], [High], [Low], [Difference], [Average], [Percentage], [InfoDate], [Hourly], [CaptureDateTime] From Rate ) AS T WHERE t.RowNo BETWEEN 200*@PageNumber AND 200 * (@PageNumber+1)-1 ORDER BY RowNo DESC 

It's me that I used ...