CREATE PROCEDURE 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
CREATE TABLE #TempItems
(
 ID int IDENTITY,
 Name varchar(50),
 Price currency
)
-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempItems (Name, Price)
SELECT Name,Price FROM tblItem ORDER BY 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 = 
 (
  SELECT COUNT(*) 
  FROM #TempItems TI
  WHERE TI.ID >= @LastRec
 ) 
FROM #TempItems
WHERE ID > @FirstRec AND ID < @LastRec
-- Turn NOCOUNT back OFF
SET NOCOUNT OFF
 
ref: http://www.aspfaqs.com/webtech/062899-1.shtml
ref: http://technet.microsoft.com/en-us/library/ms174998.aspx