게시판 페이징 SP 예제

 

CREATE PROC [dbo].[PROJECT_MEMBERLIST_S]
     @NOW_PAGE INT -- 현재 페이지 번호
     ,@NICKNAME VARCHAR(30) -- 제목
     ,@SDATE VARCHAR(23) -- 검색시작날짜
     ,@EDATE VARCHAR(23) -- 검색종료날짜
 
AS

 

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

 

DECLARE @SQL NVARCHAR(MAX)

DECLARE @START_NO INT
DECLARE @END_NO INT
DECLARE @PAGE_SIZE INT

SET @PAGE_SIZE=10 -- 페이징 사이즈
SET @START_NO = (@NOW_PAGE -1) * @PAGE_SIZE
SET @END_NO = (@NOW_PAGE * @PAGE_SIZE)+1

DECLARE @PARAM_DEF NVARCHAR(MAX)

SET @SQL =
N'
SELECT 
     ROW_NUM
     , MEMB_SEQ
     , NICKNAME 
     , TOTAL_POINT
     , CONVERT(CHAR(16), INS_DATE, 20) AS INS_DATE FROM
(
     SELECT
          ROW_NUMBER() OVER (ORDER BY EM.INS_DATE DESC) AS ROW_NUM
          , EM.*
          , ERS.TOTAL_POINT
     FROM EG_MEMBER EM WITH(NOLOCK)
     LEFT JOIN EG_RECORD_STATUS ERS
     ON EM.MEMB_SEQ = ERS.MEMB_SEQ
     WHERE EM.MEMB_SEQ >0'
 
     IF @NICKNAME <> ''
         BEGIN 
           SET @SQL= @SQL + N' AND NICKNAME LIKE ''%'+@NICKNAME+'%'''
          END

     IF @SDATE <> ''
         BEGIN
           SET @SQL= @SQL + N' AND EM.INS_DATE > CONVERT(DATETIME,@V_SDATE)'
         END
     IF @EDATE <> ''
         BEGIN
           SET @SQL= @SQL + N' AND EM.INS_DATE < CONVERT(DATETIME,@V_EDATE)'
     END  

 

SET @SQL= @SQL + N' )
                AS BOARD
                WHERE ROW_NUM > @V_START_NO AND ROW_NUM < @V_END_NO'

 
SET @PARAM_DEF = N' @V_START_NO INT
      , @V_END_NO INT
      , @V_SDATE VARCHAR(23)
      , @V_EDATE VARCHAR(23)'

EXECUTE SP_EXECUTESQL @SQL, @PARAM_DEF
     , @V_START_NO = @START_NO
     , @V_END_NO =@END_NO 
     , @V_SDATE =@SDATE 
     , @V_EDATE =@EDATE