-- ROW_NUMBER() 페이징

-- 샘플 테이블
Create Table dbo.Divisions
(
	DivisionId Int Identity(1, 1) Not Null Primary Key,
	DivisionName NVarChar(50) Not Null
)
Go

-- 테이블의 모든 정보를 반환해 봅니다.
Select * From Divisions;
Go

-- 200건의 샘플 데이터 입력
Declare @Name NVarChar(50)
Declare @i Int
Set @i = 1
While @i <= 200
Begin
	Set @Name = '분류' + Cast(@i As NVarChar)
	Insert Into Divisions(DivisionName) Values(@Name)
	Set @i = @i + 1
End
Go

-- 테이블의 모든 정보를 반환해 봅니다.
Select * From Divisions;
Go

-- 페이징 처리를 위한 ROW_NUMBER() 함수의 의미를 알아봅니다.
Select ROW_NUMBER() Over(Order By DivisionId) As RowNumber, * From Divisions;
Go

-- 홀수 데이터 삭제
Delete From Divisions Where DivisionId % 2 = 1;
Go

-- 페이징 처리를 위한 ROW_NUMBER() 함수의 의미를 알아봅니다.
Select ROW_NUMBER() Over(Order By DivisionId Asc) As RowNumber, * From Divisions;
Go

-- ROW_NUMBER()의 결과값을 임시로 받아서 이를 통해서 페이징 처리를 합니다.
-- 11번부터 20번까지의 데이터만 출력
Select * 
From 
	(Select ROW_NUMBER() Over(Order By DivisionId Asc) As RowNumber, * From Divisions) 
		As TempRowTables 
Where 
	RowNumber >= 11 And RowNumber < (11 + 10)
;
Go

-- With 절 사용
With TempRowTables
As
(
	Select ROW_NUMBER() Over(Order By DivisionId Asc) As RowNumber, * From Divisions
)
Select * From TempRowTables Where RowNumber Between 11 And (11 + 10 - 1);
Go

-- 페이징 처리 함수
Create Function GetDivisionsPage(@Page Int, @PageSize Int)
	Returns Table
As
	Return
		With TempRowTables
		As
		(
			Select ROW_NUMBER() Over(Order By DivisionId Asc) As RowNumber, * From Divisions
		)
		Select * From TempRowTables Where RowNumber 
			Between (@Page - 1) * @PageSize + 1 And (@Page * @PageSize) 
Go

-- 페이징 함수 사용
Select * From GetDivisionsPage(1, 10);
Select * From GetDivisionsPage(2, 20);
Select * From GetDivisionsPage(3, 5);
Go

-- 페이징 처리 저장 프로시저
Create Procedure GetDivisionsWithIndex
	@StartRowIndex Int,
	@MaxRows Int	
As
	Select * 
	From 
		(Select ROW_NUMBER() Over(Order By DivisionId Asc) As RowNumber, * From Divisions) 
			As TempRowTables 
	Where 
		RowNumber >= @StartRowIndex And RowNumber < (@StartRowIndex + @MaxRows)
Go

-- 페이징 처리 저장 프로시저 사용
Exec GetDivisionsWithIndex 11, 10
Go
Exec GetDivisionsWithIndex 11, 5
Go

-- 페이징 처리 저장 프로시저 2
Create Procedure GetDivisionsWithPage
	@Page Int,
	@PageSize Int	
As
	Select * 
	From 
		(Select ROW_NUMBER() Over(Order By DivisionId Asc) As RowNumber, * From Divisions) 
			As TempRowTables 
	Where 
		RowNumber Between (@Page - 1) * @PageSize + 1 And (@Page * @PageSize) 
Go

-- 페이징 처리 저장 프로시저 사용
Exec GetDivisionsWithPage 5, 10
Go
Exec GetDivisionsWithPage 2, 5
Go