ORDER BY OFFSET n ROWS FETCH NEXT n ROWS ONLY
Available only from SQL Server 2011 (Denali).
SELECT ..... FROM .....
ORDER BY ColumnA
OFFSET 10 ROWS -- skip the first 10 rows
FETCH NEXT 10 ROWS ONLY --return only 10 rows
Until MSSQL 2011 we do 'Paging' using:
SELECT ..., ROW_NUMBER ( ) OVER ( ORDER BY columnA) AS ROW FROM ... WHERE ROW BETWEEN 51 AND 100
And here a sample to SP that return a page in paging list:
CREATE PROCEDURE SqlPagingSP
(
@PageNumber int,
@RowsPerPage int
)
AS
SELECT .....
FROM ......
ORDER BY ColumnA
OFFSET (@PageNumber-1)*@RowsPerPage ROWS
FETCH NEXT @RowsPerPage ROWS ONLY
GO
No comments:
Post a Comment