set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[sp_Paging_RowNumber]
 @SqlStmt nvarchar(max),
 @PageIndex int = 0,
 @PageSize int = 10,
 @SqlCountStmt nvarchar(max)
AS
BEGIN

 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

 /*Default Page Number*/
 IF @PageIndex < 0 AND @PageSize < 0
 BEGIN
 
    /*Execute dynamic query*/ 
 EXEC( '
   WITH TempTCE AS (' + @SqlStmt + ')

   SELECT *
   FROM TempTCE
    '
 )

 END

 ELSE
 BEGIN

 IF @PageIndex < 0
  SET @PageIndex = 0

    DECLARE @strStartRowNumber varchar(50)
 DECLARE @strEndRowNumber varchar(50)
 SET @strStartRowNumber = CAST( ((@PageIndex)*@PageSize+1) AS varchar(50))
 SET @strEndRowNumber = CAST( (@PageIndex + 1)* @PageSize AS varchar(50))

    /*Execute dynamic query*/ 
 EXEC( '
   WITH TempTCE AS (' + @SqlStmt + ')

   SELECT *
   FROM TempTCE
   WHERE RowNumber BETWEEN ' + @strStartRowNumber + ' AND '+ @strEndRowNumber + '
    '
 )
 
 END

 EXEC( @SqlCountStmt )

END