← Tutti gli articoli

TSQL : Paging with ROW_NUMBER

06 aprile 2011  · TSQL · Article  ·  614 visite

SQL Server (from 2005) has a new ROW_NUMBER() function that makes paging task easier.

DECLARE               @PageNum int =1
DECLARE    @PageSize int =10
declare @TotalPages int
WITH ContentsRowNumber AS
(
SELECT ROW_NUMBER() OVER(ORDER BY c.ContentDate desc) AS RowNum,
c.ContentId
FROM dbo.Contents c   inner join ContentsHistory h on c.ContentId=h.ContentId
where h.ContentStatusID = 1  --1=Published
and h.FinishDate is null
)
SELECT r.RowNum, v.*
FROM vwContents v
inner join  ContentsRowNumber r on v.ContentId = r.ContentId
WHERE
v.FinishDate is null
and r.RowNum BETWEEN (@PageNum - 1) * @PageSize + 1
AND (@PageNum * @PageSize)
;