[sql2008r2] 정적쿼리 + 페이징 + 컬럼별 정렬

Sql 2008 R2 기준으로 작성된 쿼리 입니다. 상위 버전에서는 훨씬 간단하게 페이징과 정렬을 구현할 수 있으니 자세한 사항은 msdn 참조 바랍니다.

1. 테스트를 위한 임시테이블 생성 해줍니다. 
 

DECLARE @page           INT = 1
DECLARE @offset         INT = 5     

    CREATE TABLE #TEMP_INTEREST_ITEM

    (

        [no] [int] identity (1,1),

        [itemNo] [varchar](14) NULL,

        [itemType] [char](2) NULL,

        [itemOriginalNo] [int] NULL,

        [itemOriginalType] [varchar] (100) NULL,

        [imagePath] [varchar] (4000) NULL,

        [detailPath] [varchar] (4000) NULL,

        [priceText] [varchar] (100) NULL,

        [price] [money] NULL,

        [dealType] [varchar] (100) NULL,

        [dong] [varchar] (50) NULL,

        [complexName] [varchar] (100) NULL,

        [roomCount] [int] NULL,

        [areaText] [varchar] (100) NULL,        

        [area] [decimal] NULL,

        [evaluation] [decimal] NULL,

        [etc] [varchar] (4000) NULL,

        [date] [char](10) NULL,

        [registDate] [datetime] NULL

    )   

2. 쿼리 부분입니다. 

SELECT [rowNum], [no], [itemNo], [itemType], [itemOriginalNo], [itemOriginalType], [imagePath], [detailPath], [priceText], [price], [dealType], [dong], [complexName], [roomCount], [areaText], [area], [evaluation], [etc], [date], [registDate]

         , @itemTotalCount AS totalCount 

    FROM (

        SELECT ( CASE 

                    WHEN @orderDirection = 'ASC' AND @orderTarget = 'Date' THEN ROW_NUMBER() OVER (ORDER BY [date])

                    WHEN @orderDirection = 'ASC' AND @orderTarget = 'Price' THEN ROW_NUMBER() OVER (ORDER BY [price])

                    WHEN @orderDirection = 'ASC' AND @orderTarget = 'Area' THEN ROW_NUMBER() OVER (ORDER BY [area])                 

                    WHEN @orderDirection = 'ASC' AND @orderTarget = 'Evaluation' THEN ROW_NUMBER() OVER (ORDER BY [evaluation])

                    WHEN @orderDirection = 'DESC' AND @orderTarget = 'Date' THEN ROW_NUMBER() OVER (ORDER BY [date] DESC)

                    WHEN @orderDirection = 'DESC' AND @orderTarget = 'Price' THEN ROW_NUMBER() OVER (ORDER BY [price] DESC)

                    WHEN @orderDirection = 'DESC' AND @orderTarget = 'Area' THEN ROW_NUMBER() OVER (ORDER BY [area] DESC)                                       

                    WHEN @orderDirection = 'DESC' AND @orderTarget = 'Evaluation' THEN ROW_NUMBER() OVER (ORDER BY [evaluation] DESC)

                    ELSE ROW_NUMBER() OVER (ORDER BY tii.[no])

                END) AS rowNum

             , [no], [itemNo], [itemType], [itemOriginalNo], [itemOriginalType], [imagePath], [detailPath], [priceText], [price], [dealType], [dong], [complexName], [roomCount], [areaText], [area], [evaluation], [etc], [date], [registDate]

          FROM #TEMP_INTEREST_ITEM AS tii

     ) AS a

     WHERE rowNum > ( CASE WHEN @page > 1 THEN ( @page - 1 ) * @offset ELSE 0 END ) 

       AND rowNum <= ( @page * @offset )

  ORDER BY a.rowNum