■ WITH문을 사용해 페이징 처리를 하는 방법을 보여준다.
▶ 페이징 조회하기 (WITH문) 예제 (SQL)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
DECLARE @SourceSQL NVARCHAR(MAX); DECLARE @RowNumberOrder NVARCHAR(300); DECLARE @CurrentPage NVARCHAR(100); DECLARE @LineCountPerPage NVARCHAR(100); SET @SourceSQL = 'SELECT A.* FROM PhotoBook AS A WHERE A.MenuID = 44'; SET @RowNumberOrder = 'A.ID ASC'; SET @CurrentPage = '2'; SET @LineCountPerPage = '20'; EXECUTE SelectPage @SourceSQL, 'A.ID ASC', 2, 20; |
▶ 페이징 조회하기 (WITH문) (SQL)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
CREATE PROCEDURE [dbo].[SelectPage] ( @SourceSQL NVARCHAR(MAX) -- 소스SQL문 ,@RowNumberOrder NVARCHAR(300) -- ROW_NUMBER 함수정렬 ,@CurrentPage NVARCHAR(100) -- 현재페이지 ,@LineCountPerPage NVARCHAR(100) -- 페이지당라인수 ) AS BEGIN SET NOCOUNT ON; DECLARE @SQL NVARCHAR(MAX); SET @SQL = N' WITH #SourceTable AS ( SELECT ROW_NUMBER() OVER(ORDER BY ' + @RowNumberOrder + ') AS RowNumber, A.* FROM ( ' + @SourceSQL + ' ) AS A ) SELECT A.* FROM #SourceTable AS A WHERE A.RowNumber BETWEEN ((' + @CurrentPage + ' - 1) * ' + @LineCountPerPage + ' + 1) AND (' + @CurrentPage + ' * ' + @LineCountPerPage + '); '; EXECUTE SP_EXECUTESQL @SQL; END GO |