1. TOP 키워드 이용 페이징
create procedure [dbo].[PaymentList1]
@page int,
@pageSize int
as
BEGIN
select top (@pageSize)
execDate, paymentID
from tblPayment
where paymentID not in (
select top ((@page - 1) * @pageSize) paymentID
from tblPayment
ORDER BY paymentID desc
)
ORDER BY paymentID desc
end
2. RowCount 키워드 이용 페이징
create procedure [dbo].[PaymentList2]
@page int,
@pageSize int
as
BEGIN
DECLARE @N1 INT, @N2 INT
declare @lastIndex int
set @N1 = ((@page - 1) * @pageSize)
set @N2 = @pageSize
SET ROWCOUNT @N1
select
@lastIndex = paymentID -- 마지막 로우에서 반환된 값을 임시 저장합니다
from tblPayment
ORDER BY paymentID desc
SET ROWCOUNT @N2
select
execDate, paymentID
from tblPayment
where paymentID < @lastIndex
ORDER BY paymentID desc
end
3. Row_Number, CTE 이용 페이징
alter procedure [dbo].[PaymentList3]
@page int,
@pageSize int
as
BEGIN
with paymentListTBL as
(
select Row_Number() OVER (ORDER BY paymentID desc) AS rowNum,
execDate, paymentID
from
tblPayment
)
select * from paymentListTBL
where rowNum between ((@page - 1) * @pageSize) + 1 and @page * @pageSize
end