Skip to content

Instantly share code, notes, and snippets.

@menacestudio
Created November 13, 2014 03:19
Show Gist options
  • Save menacestudio/b9768f7ffa006d6a201b to your computer and use it in GitHub Desktop.
Save menacestudio/b9768f7ffa006d6a201b to your computer and use it in GitHub Desktop.
Sample paging in TSQL using common table expression (CTE)
-- 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment