CTE 방식으로 게시판 List Data가져오기

CREATE PROCEDURE [dbo].[UP_GIFT_Corporation_List]
(  
    @CurrentPageIndex           INT      -- 목록의 현재페이지 번호      
,   @PageSize                   INT      -- 목록의 한 페이지에 표현되는 글 목록수 
,   @SearchCondition            varchar(4000)           -- 조회조건 내용       
, @SortCondition    NVARCHAR(100)   -- 조회정렬 조건
, @Lang      NVARCHAR(10)   -- 언어
)  
AS 
SET NOCOUNT ON

DECLARE @StNum                  int  
SET     @StNum                  = (@PageSize * (@CurrentPageIndex - 1)) + 1  
DECLARE @FnNum                  int  
SET     @FnNum                  = @PageSize * @CurrentPageIndex  
DECLARE @SqlString              nvarchar(4000)   
DECLARE @SqlTotalRowCount       nvarchar(4000) 
DECLARE @ISChangeYN             char(1) 

SET @ISChangeYN = 'N'
IF charindex('Name',@SortCondition)+charindex('CATEGORY_NAME',@SortCondition)+charindex('PSEngName',@SortCondition) > 0
BEGIN
 SET @ISChangeYN = 'Y'
END

IF @ISChangeYN = 'N'
BEGIN
 SET @SortCondition = 'C.'+@SortCondition
END

SET     @SqlString = '  
WITH ORIGINAL_DATA AS ( 
SELECT 
 C.CPGUID
 ,C.TypeCD
 ,cg.CATEGORY_NAME as Type
 ,C.CPEngName
 ,C.PSGUID
 ,V.PSEngName
 ,C.CountryCD
 ,N.Name as Country
 ,C.Tel
 ,CONVERT(VARCHAR(10), C.ModifyDT, 120) as ModifyDT
 ,ROW_NUMBER() OVER(ORDER BY '+ @SortCondition+ ') AS RowNo 
FROM        dbo.TB_GIFT_Corporation  C
  LEFT OUTER JOIN dbo.TB_GIFT_VIPPerson V
  ON C.PSGUID = V.PSGUID
  LEFT OUTER JOIN CmnMgt.dbo.tb_Nation N
  ON C.CountryCD = N.ID
  LEFT OUTER JOIN CmnMgt.dbo.tb_Category Cg
  ON C.TypeCD = Cg.CATEGORY_CODE
WHERE   C.Active = ''Y'' '

SET @sqlTotalRowCount = '
SELECT
        COUNT(*) AS TOTAL_ROW_CNT
FROM        dbo.TB_GIFT_Corporation  C
  LEFT OUTER JOIN dbo.TB_GIFT_VIPPerson V
  ON C.PSGUID = V.PSGUID
  LEFT OUTER JOIN CmnMgt.dbo.tb_Nation N
  ON C.CountryCD = N.ID
  LEFT OUTER JOIN CmnMgt.dbo.tb_Category Cg
  ON C.TypeCD = Cg.CATEGORY_CODE
WHERE   C.Active = ''Y'' '

SET  @SqlString = @sqlString + @SearchCondition  
SET  @SqlTotalRowCount = @SqlTotalRowCount + @SearchCondition  

SET @SqlString = @SqlString + ' 
) 
 
SELECT * FROM ORIGINAL_DATA WHERE RowNo BETWEEN ' + CONVERT( varchar, @StNum ) + ' AND ' + CONVERT(varchar, @FnNum)  

PRINT  @SqlString

exec sp_executesql @SqlString

exec sp_executesql @SqlTotalRowCount