[MS-SQL] 저장프로시저 페이징 예제 1

 

 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
 
ALTER PROCEDURE [dbo].[sp_pay_suryo_list]
    @table     varchar(30),
    @Limit    INT,           -- 페이지당 레코드 수
    @Page     INT,           -- 페이지 번호 (1부터)
    @Lec_code VARCHAR(50) = NULL     -- 강의 코드
 
AS
 
BEGIN
    SET NOCOUNT ON;
 
    -------------------------------------------------
    -- 1. 기본 방어
    -------------------------------------------------
    IF (@Limit IS NULL OR @Limit <= 0)
    BEGIN
        RAISERROR ('@Limit must be > 0', 16, 1);
        RETURN;
    END
 
    IF (@Page IS NULL OR @Page <= 0)
    BEGIN
        SET @Page = 1;
    END
 
    -------------------------------------------------
    -- 2. 페이징 계산값
    -------------------------------------------------
    DECLARE @n_limit INT;
    DECLARE @n_range INT;
 
    SET @n_limit = @Limit;
    SET @n_range = (@Limit * @Page);
 
    -------------------------------------------------
    -- 3. 조건절 구성 (강의코드가 있을 경우 WHERE 절에 포함)
    -------------------------------------------------
    DECLARE @WhereCond VARCHAR(300) = '';
 
    IF (@Lec_Code IS NOT NULL AND LTRIM(RTRIM(@Lec_Code)) <> '')
        SET @WhereCond = ' WHERE LEC_CODE = ''' + @Lec_Code + '''';
 
    -------------------------------------------------
    -- 4. 전체 건수 조회 (조건 반영)
    -------------------------------------------------
    DECLARE @TotalCountSql NVARCHAR(400);
    DECLARE @TotalCount INT;
 
    SET @TotalCountSql = 'SELECT @TC = COUNT(*) FROM ' + @Table + ' (NOLOCK) ' + @WhereCond;
 
    EXEC sp_executesql
        @TotalCountSql,
        N'@TC INT OUTPUT',
        @TC = @TotalCount OUTPUT;
 
    -------------------------------------------------
    -- 5. 실제 데이터 조회 SQL 구성
    -------------------------------------------------
    DECLARE @Sql NVARCHAR(MAX);
 
    SET @Sql = 'SELECT ' +
        'SEQ, UID, NAME, UNIV_ID, UNIV_NAME, ' +
        'LEC_CODE, LEC_NAME, LEC_TID, ' +
        'PRICE, PAY_METHOD, PAY_TYPE, PAY_UNIT, ' +
        'ORDER_DATE, PAY_DATE, MEMO, ' +
        'lec_start, lec_end, txn_id, OID, RECEIPT, STATE, ' +
        'suryo_num, poll, ' +
        CAST(@TotalCount AS VARCHAR(20)) + ' AS TOTAL_COUNT ' +
    'FROM ' + @Table + ' (NOLOCK) ' +
    'WHERE SEQ IN (' +
    '   SELECT TOP ' + CAST(@n_limit AS VARCHAR(20)) + ' SEQ FROM (' +
    '       SELECT TOP ' + CAST(@n_range AS VARCHAR(20)) + ' SEQ FROM ' + @Table + ' (NOLOCK) ' +
            @WhereCond +  -- 조건절 포함
    '       ORDER BY SEQ DESC' +
    '   ) AS a ' +
    '   ORDER BY SEQ ASC' +
    ') ' +
    'ORDER BY SEQ DESC;';
 
    -------------------------------------------------
    -- 6. 실행
    -------------------------------------------------
    EXEC(@Sql);
END
 
---------------------------
--EXEC sp_pay_suryo_list
--    @Table = '_ORDER_B2C',
--    @Limit = 30,
--    @Page = 1,
--    @Lec_Code = '258';
----------------------------