[DATABASE/SQLSERVER] 페이징 조회하기 (SELECT문)

▶ 페이징 조회하기 (SELECT문) 예제

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;

 

728x90

 

▶ 페이징 조회하기 (SELECT문)

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'
SELECT A.*
FROM
(
    SELECT
        ROW_NUMBER() OVER(ORDER BY ' + @RowNumberOrder + ') AS RowNumber,
        A.*
    FROM
    (
        ' + @SourceSQL + '
    ) AS A
) AS A
WHERE A.RowNumber BETWEEN ((' + @CurrentPage + ' - 1) * ' + @LineCountPerPage + ' + 1) AND (' + @CurrentPage + ' * ' + @LineCountPerPage + ');
';

    EXECUTE SP_EXECUTESQL @SQL;
END
GO