Database Paging by stored procedure

I want a stored procedure to Take x number of data rows for the y number of page. For example

i have 20 datarow my page size is 2 if i choose page 2 i will get data row 17,18

I can use top 200 and use order by to select first and the last d开发者_Go百科atarow but how do i get pages in between.

@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

End this is what i have now but its not working properly

Ok, since you haven't specified what RDBMS you are using, I can give you a solution that's valid at least for 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 know that in the next version of SQL Server ("Denali") this will be a lot easier with some modifications that were made to the TOP clause.

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

This i what i used...