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

 
<!-- This assumes you already have a recordset object
explicitly created name objRS -->

<%
	'How many records per page do we want to show?
	Const iRecordsPerPage = 10

	Dim currentPage	 'what page are we on??
	Dim bolLastPage	 'are we on the last page?
	
	if len(Request.QueryString("page")) = 0 then
		currentPage = 1
	else
		currentPage = CInt(Request.QueryString("page"))
	end if

	'Show the paged results
	strSQL = "sp_PagedItems " & currentPage & "," & iRecordsPerPage
	objRS.Open strSQL, objConn
	
	'See if we're on the last page
	if Not objRS.EOF then
		if CInt(objRS("MoreRecords")) > 0 then
			bolLastPage = False
		else
			bolLastPage = True
		end if
	end if
%>
<P>

<TABLE BORDER=0 CELLSPACING=1 CELLPADDING=4 ALIGN=CENTER>
<TR><TH COLSPAN=2 BGCOLOR=NAVY>
	<FONT SIZE=+1 COLOR=WHITE>
		List of Items
	</FONT>
</TH></TR>
<%
	Do While Not objRS.EOF %>
		<TR><TD ALIGN=LEFT BGCOLOR=GRAY>
			<%=objRS("Name")%>
		</TD><TD ALIGN=CENTER BGCOLOR=GRAY>
			<%=FormatCurrency(objRS("Price"))%>
		</TD></TR>
<%		objRS.MoveNext
	Loop %>
</TABLE>
<P>
<CENTER>
<%
'Only show the previous button if we are NOT on the first page
if currentPage > 1 then %>
  <INPUT TYPE=BUTTON VALUE="<< Previous <%=iMaxRecords%> Records"
  ONCLICK="document.location.href='thispage.asp?page=<%=currentPage-1%>'">
       
<% end if 

'Only show the next button if we are NOT on the last page
if Not bolLastPage then %>
  <INPUT TYPE=BUTTON VALUE="Next <%=iMaxRecords%> Records >>"
  ONCLICK="document.location.href='thispage.asp?page=<%=currentPage+1%>'">
<% end if %>
</CENTER>