SQL Server 2K Paging

Hi all, for this tip I'll use Microsoft Northwind database.

The question here is: A have a lot of data and need to page it via SQL Server, how can I do it?
Sounds complicated but is simple.

All you need is two variables to determine the range you want to page and which row the paging will start.
The result can be ordered by many columns, so we need to create an identity column that will keep a sequential number wich will be used to unique identify each table row.

CREATE PROCEDURE GetProductsByRange_Pr
-- Var used to indicate which row will start to count
@RowStart INT,
-- Var used to determinate how many rows will be returned
@PageSize INT
AS

-- Create a temporary table to keep products to be paged
CREATE TABLE #TempProducts(
ID int IDENTITY,
ProductID int
)

-- Insert the rows in the previous created temp table
INSERT INTO #TempProducts (ProductID)
 SELECT ProductID FROM Products

-- Retrieve data from temp table
SELECT
 T.ID,
 P.*
FROM
 #TempProducts T
 INNER JOIN Products P ON T.ProductID = P.ProductID
WHERE
 T.ID BETWEEN @RowStart AND (@RowStart + @PageSize) - 1

Then you call your procedure:

-- Get 10 rows starting from row number 1
exec GetProductsByRange_Pr 1, 10

-- Get 10 rows starting from row number 11
exec GetProductsByRange_Pr 11, 10

-- Get 10 rows starting from row number 21
exec GetProductsByRange_Pr 21, 10

Thanks for read!

See yaa!

Andre Baltieri
andre.baltieri@insidedotnet.com.br