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

最新推荐文章于 2025-10-30 11:15:54 发布

原创最新推荐文章于 2025-10-30 11:15:54 发布 · 1.3k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sqlserver #数据库 #variables #table #asp.net #insert

SQL 专栏收录该内容

4 篇文章

订阅专栏

博客介绍了能检索数据库指定页记录的存储过程，页面大小可自定义，适用于分页显示场景。给出了具体的SQL Server存储过程代码，包括创建临时表、插入数据、计算记录范围和选择指定页记录等操作。

些存贮过程能检索数据库中指定页的记录，页面大小可由自己指定，能适合用于需要分页显示的场合，比较灵活。（源于《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