Working with my database administrator, I developed a stored procedure for use with SQL Server that puts the burden on the SQL Server to only return the required number of records to the web server instead of the entire database table. This GREATLY reduces the load factor on the web server when making database requests. The explanation of the code follows the stored procedure. In this example, we are retrieving item pricing information from a table and returning N records.
CREATE PROCEDURE CREATE PROCEDURE usp_RoomsPagedItems
(
@Page int
)
AS
-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON
DECLARE @RecsPerPage int
-- Find out where we will start our records from
DECLARE @RecCount int
SET @RecsPerPage = 20
SELECT @RecCount = @RecsPerPage * @Page + 1
--Create a temporary table
CREATE TABLE #TempItems
(
ID int IDENTITY,
ROOMNAME varchar(50),
ROOMID int,
ROOMTYPEID int
)
-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempItems (ROOMNAME, ROOMID,ROOMTYPEID)
SELECT 'ROOM ' + ROOM.ROOMNO AS ROOMNAME,
ROOM.ID AS ROOMID,
ROOMTYPE.ID AS ROOMTYPEID
FROM DBO.ROOMTYPE INNER JOIN DBO.ROOM ON ROOM.ROOMTYPEID = ROOMTYPE.ID
--ORDER BY CAST(ROOM.ROOMNO AS NUMERIC)
ORDER BY ROOM.ROOMNO
-- 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
