CREATE PROCEDURE dbo.USP_List
(
@PageIndex int , --第几页
@PageSize int --每页要显示多少数据
)
As
Begin
-- 设置页面范围
DECLARE @PageLowerBound int ---所取记录的下限
DECLARE @PageUpperBound int ---所取记录的上限
DECLARE @TotalRecords int ---全部记录数目
SET @PageLowerBound = @PageSize * (@PageIndex-1) ---计算下限值
SET @PageUpperBound = @PageSize - 1 + @PageLowerBound ---计算上限值 -- 创建临时表保存记录结果
CREATE TABLE #PageIndexForTempList
(
IndexId int IDENTITY (0, 1) NOT NULL,
tabID int
)
-- 将数据插入到临时表中
INSERT INTO #PageIndexForTempList(HB_tabID)
Select tabID From TableName
where 条件信息
order tabID Desc SELECT @TotalRecords = @@ROWCOUNT
Select tabID,tabcol1....
From TableName tab,#PageIndexForTempListp
where
tab.tabID = p.tabID
and p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
return @TotalRecords
End
SET @pagenum = 2
SET @pagesize = 3
SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY newsid DESC) AS rownum,
newsid, topic, ntime, hits
FROM news) AS D
WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize
ORDER BY newsid DESC