첨부 소스 코드는 나눔고딕코딩 폰트를 사용합니다.
728x90
반응형
728x170

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

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

 

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

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
728x90
반응형
그리드형(광고전용)
Posted by icodebroker

댓글을 달아 주세요