You could use a stored procedurelike this:
CREATEPROCEDURE sp_PagedItems
(
@Page int,
@RecsPerPage int )
AS -- We don't want to return the # of rows inserted -- into our temporary table, so turn NOCOUNT ON SET NOCOUNT ON --Create a temporary table CREATETABLE #TempItems
(
ID intIDENTITY,
Name varchar(50),
Price currency
)
-- Insert the rows from tblItems into the temp. table INSERTINTO #TempItems (Name, Price)
SELECT Name,Price FROM tblItem ORDERBY Price
-- Find out the first and last record we want DECLARE @FirstRec int, @LastRec int SELECT @FirstRec = (@Page -1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage +1)
-- Now, return the set of paged records, plus, an indiciation of we -- have more records or not! SELECT*,
MoreRecords = (
SELECTCOUNT(*)
FROM #TempItems TI
WHERE TI.ID >= @LastRec
)
FROM #TempItems
WHERE ID > @FirstRec AND ID < @LastRec
-- Turn NOCOUNT back OFF SET NOCOUNT OFF