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
'프로그래머 > 프로그래밍' 카테고리의 다른 글
SVN branches, trunk, tags 디렉토리 의미 (0) | 2013.04.23 |
---|---|
select 구문 단축키 설정 (0) | 2013.04.22 |
Visual Studio에서 classview 안나올때 (0) | 2013.03.07 |
이클립스 단축키 (CTRL+TAB) (0) | 2013.02.14 |
cocos2d-x: Cannot open include file: sched.h (0) | 2012.11.23 |
댓글