CarlHui

A light heart lives long.

检索数据库中指定页的记录(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

阅读更多
个人分类: SQL
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭