[MSSQL] 계층형 + 페이징 게시판

/**************************************************************************************************/

/**************************************************************************************************/

IF OBJECT_ID('dbo.uspGetGoodsQnaPagingList','P') IS NOT NULL 

 BEGIN

DROP  PROCEDURE dbo.uspGetGoodsQnaPagingList

 END

GO

CREATE PROCEDURE dbo.uspGetGoodsQnaPagingList

@oidStoreGroup INT

, @oidStore INT

, @oidGoodsMaster INT

, @oidGoodsDetail INT

, @page INT

, @totalCount INT OUTPUT

--------------WITH ENCRYPTION

AS

/*!

 *  \brief 제품 Q&A

 * \remarks

 *  \author

 * \test

DECLARE @totalCount INT

EXEC uspGetGoodsQnaPagingList 10,100111,497569,2,1,@totalCount OUTPUT

SELECT @totalCount

 */

BEGIN

SET NOCOUNT ON

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SET LOCK_TIMEOUT 3000

SET XACT_ABORT ON

             

-- 1:성공, 0: 기본 오류값, 음수:오류코드

-- SET @rv = 0

/**********************************************************************************************

** 작업시작(이하에만기술)

**********************************************************************************************/

DECLARE @pagesize INT = 10

IF ( @page IS NULL OR @page = 0 )

SET @page = 1

SELECT 

@totalCount = COUNT(*)

FROM go_goodsQna 

WHERE n1Deleted = 0

AND oidStoreGroup = @oidStoreGroup

AND oidStore = @oidStore

AND oidGoodsMaster = @oidGoodsMaster

AND oidGoodsDetail = @oidGoodsDetail

;WITH tree_query AS ( 

SELECT 

oidStoreGroup

, oidStore

, oidQna 

, oidFKQna

, oidQna AS sort

, n4Level

, strContent

, oidRegStore

, isPrivate

, n1Deleted

FROM go_goodsQna 

WHERE n1Deleted = 0

AND oidStoreGroup = @oidStoreGroup

AND oidStore = @oidStore

AND oidGoodsMaster = @oidGoodsMaster

AND oidGoodsDetail = @oidGoodsDetail

AND n4Level = 0

UNION ALL 

SELECT 

B.oidStoreGroup

, B.oidStore

, B.oidQna 

, B.oidFKQna 

, B.oidFKQna AS sort

, B.n4Level

, B.strContent

, B.oidRegStore

, B.isPrivate

, B.n1Deleted

FROM go_goodsQna B

INNER JOIN tree_query C 

ON B.oidFKQna = C.oidQna 

WHERE B.n1Deleted = 0

AND B.oidStoreGroup = @oidStoreGroup

AND B.oidStore = @oidStore

AND B.oidGoodsMaster = @oidGoodsMaster

AND B.oidGoodsDetail = @oidGoodsDetail

AND B.n4Level > 0

) 

, PagingList AS (

SELECT 

ROW_NUMBER() OVER ( ORDER BY sort, n4Level ) AS [rownum]

, * 

FROM tree_query 

)

SELECT * FROM PagingList WHERE rownum BETWEEN ( ( @page - 1 ) * @pagesize ) + 1 AND  @page * @pagesize