Sample paging in TSQL using common table expression (CTE)

sample_cte_paging.sql

	-- Calculate paging
	DECLARE @topRecord VARCHAR(20) = CAST(((@pageSize+@offset)+1) AS VARCHAR) 
	DECLARE @cte_query NVARCHAR(MAX)

	SET @cte_query = WITH cte_res
	AS ( SELECT ROW_NUMBER() OVER(ORDER BY ['+@sortBy+'] '+@sortDir+') AS rowid, count(*) over() as TotalRecords,* from (SELECT * FROM #someTable WITH(NOLOCK)) as table1)
	select rowID, TotalRecords, id, someField from cte_res with(nolock) where rowid>'+ CAST(@offset AS VARCHAR) +' and rowid< ' + @topRecord

	EXECUTE sp_executesql @cte_query