CREATE PROCEDURE [dbo].[spr_PagedItems]
@Page int,
@RecsPerPage int,
@Tournement 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
(
tempID int IDENTITY,
ScoreID int,
GameID int,
PlayerID int
)
-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempItems (ScoreID, GameID, PlayerID)
SELECT ScoreID, GameID, PlayerID FROM Scores WHERE tID = @Tournement ORDER
BY ScoreTotal DESC
-- 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 *, (SELECT COUNT(tempID) FROM #TempItems TI WHERE TI.tempID >=
@LastRec) AS MoreRecords
FROM #TempItems
WHERE tempID > @FirstRec AND tempID < @LastRec
-- Turn NOCOUNT back OFF
SET NOCOUNT OFF
-----Original Message-----
From: Calvin Ward [mailto:[EMAIL PROTECTED]
Sent: Monday, December 22, 2003 13:30
To: CF-Talk
Subject: SQL Server 2000 and Pagination
Happy Holidays!
I'm curious how folks are implementing pagination using SQL Server 2000 and
avoiding returning entire recordsets to CF.
In other words, if I have a search result set of 1,000 rows, and the user
will be walking through that recordset 25 rows at a time using <prev>
<pagenumbers> <next> methods, how are folks limiting the result set to only
the current 25 rows needed to display.
Thanks,
Calvin
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
