페이징

SELECT *

FROM (

SELECT ROW_NUMBER() OVER (ORDER BY ProductID DESC, SellStartDate DESC) as RowNum

,ProductID, Name, ProductNumber, SellStartDate, ReorderPoint

FROM Production.Product

) as tb

WHERE tb.RowNum BETWEEN 0 AND 10

SELECT ProductID, Name, ProductNumber, SellStartDate, ReorderPoint

FROM Production.Product

ORDER BY ProductID, SellStartDate

OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY;

SELECT ProductID, Name, ProductNumber, SellStartDate, ReorderPoint

FROM Production.Product

ORDER BY ProductID, SellStartDate

OFFSET 491 ROWS FETCH FIRST 500 ROWS ONLY;

SELECT *

FROM (

SELECT ROW_NUMBER() OVER (ORDER BY ProductID DESC, SellStartDate DESC) as RowNum

,ProductID, Name, ProductNumber, SellStartDate, ReorderPoint

FROM Production.Product

) as tb

WHERE tb.RowNum BETWEEN 490 AND 500

SELECT ProductID, Name, ProductNumber, SellStartDate, ReorderPoint

FROM Production.Product

ORDER BY ProductID, SellStartDate

OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY;

CREATE PROCEDURE dbo.up_Get_Paging

@intPage int --Page

,@perPage int --RowCount

AS

SET NOCOUNT ON

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT ProductID, Name, ProductNumber, SellStartDate, ReorderPoint

FROM Production.Product

ORDER BY ProductID, SellStartDate

OFFSET (@intPage - 1) * @perPage ROWS FETCH FIRST @perPage ROWS ONLY;

SET NOCOUNT OFF

GO

EXEC up_Get_Paging @intPage = 1, @perPage = 10

GO

SELECT ProductID, Name, ProductNumber, SellStartDate, ReorderPoint

FROM Production.Product

ORDER BY ProductID, SellStartDate

EXEC up_Get_Paging @intPage = 2, @perPage = 10

GO