检索数据库中指定页的记录（SqlServer 2000示例)

些存贮过程能检索数据库中指定页的记录，页面大小可由自己指定，能适合用于需要分页显示的场合，比较灵活。（源于《Fast Track ASP.Net with C#》一书）

CREATE PROCEDURE sp_GetEmployeesByPage
@PageNumber?int,
@PageSize?int
AS

-- create a temporary table with the columns we are interested in
CREATE TABLE #TempEmployees
(
?ID ??int IDENTITY PRIMARY KEY,
?EmployeeID?int,
?LastName?nvarchar(20),
?FirstName?nvarchar(10),
?Title??nvarchar(30),
?TitleOfCourtesy?nvarchar(25),
?Address??nvarchar(60),
?City??nvarchar(15),
?Region??nvarchar(15),
?Country??nvarchar(15),
?Notes??ntext
)

-- fill the temp table with all the employees
INSERT INTO #TempEmployees
(
?EmployeeID,
?LastName,
?FirstName,
?Title,
?TitleOfCourtesy,
?Address,
?City,
?Region,
?Country,
?Notes
)
SELECT
?EmployeeID,
?LastName,
?FirstName,
?Title,
?TitleOfCourtesy,
?Address,
?City,
?Region,
?Country,
?Notes
FROM
? Employees ORDER BY EmployeeID ASC

-- declare two variables to calculate the range of records to extract for the specified page
DECLARE @FromID int
DECLARE @ToID int
-- calculate the first and last ID of the range of records we need
SET @FromID = ((@PageNumber - 1) * @PageSize) + 1
SET @ToID = @PageNumber * @PageSize

-- select the page of records
SELECT * FROM #TempEmployees WHERE ID >= @FromID AND ID <= @ToID
GO