Efficient paging storage process

Create   Procedure   [ Page ]  

As
-- No optimized Reverse Order
Declare   @ Pagesize   Int -- Number of records returned for one page
Declare   @ Curpage   Int -- Page number (page number) 0: First page;-1 last page.
Declare   @ Count   Int
Declare   @ ID   Int

Set   @ Pagesize = 10
Set   @ Curpage   = 1


-- Positioning
If   @ Curpage   =   - 1
Begin
  -- Last page
Set   Rowcount   @ Pagesize
Select   @ ID   = Productid From Products Order   By Productid
End

If   @ Curpage   >   0
Begin
  Set   @ Count   =   @ Pagesize   * ( @ Curpage   - 1 ) +   1
  Set   Rowcount   @ Count
  Select   @ ID   = Productid From Products Order   By Productid Desc
End

-- Return record
Set   Rowcount   @ Pagesize
Select   *   From Products Where Productid <= @ ID   Order   By Productid Desc

Set   Rowcount   0
Go