示例表是 示例数据库 Northwind 下的 Orders 表
Sql2000 下分页存储过程：
SELECT Identity(int,1,1) AS RowIndex, customerid,shipaddress into tmp
from dbo.Orders
select customerid,shipaddress from tmp
where rowindex between 9 and 16
drop table tmp
直接运行，使用了临时表
Sql2005下方法
示例表是 示例数据库 TBH下的ASPNETDB数据库 下的 tbh_Articles表
CREATE PROCEDURE [dbo].[tbh_Articles_GetArticlesByCategory]
(@CategoryID int,
@PageIndex int,
@PageSize int
)
AS
SET NOCOUNT ON
SELECT * FROM
(
SELECT tbh_Articles.ArticleID, tbh_Articles.AddedDate, tbh_Articles.AddedBy, tbh_Articles.CategoryID, tbh_Articles.Title, tbh_Articles.Abstract, tbh_Articles.Body,
tbh_Articles.Country, tbh_Articles.State, tbh_Articles.City, tbh_Articles.ReleaseDate, tbh_Articles.ExpireDate, tbh_Articles.Approved,
tbh_Articles.Listed, tbh_Articles.CommentsEnabled, tbh_Articles.OnlyForMembers, tbh_Articles.ViewCount, tbh_Articles.Votes, tbh_Articles.TotalRating,
tbh_Categories.Title AS CategoryTitle,
ROW_NUMBER() OVER (ORDER BY ReleaseDate DESC) AS RowNum
FROM tbh_Articles INNER JOIN
tbh_Categories ON tbh_Articles.CategoryID = tbh_Categories.CategoryID
WHERE tbh_Articles.CategoryID = @CategoryID
) Articles
WHERE Articles.RowNum BETWEEN (@PageIndex*@PageSize+1) AND ((@PageIndex+1)*@PageSize)
ORDER BY ReleaseDate DESC
