Last time I wrote some code to allow paging of stored procedure results in SQL Server it sucked.
That was about 3-4 years ago.
I just wrote some code to do it again, and well… It still sucks.
Perhaps I’m doing it wrong, but if you are looking for, “how to implement paging in sql server”, or “how to page sql server stored procedure results”, or “sql server paging”, I’ll give you my best answer for it at this point.
How I roll with CTEs
CTEs or Common Table Expressions, are a pretty nice feature of SQL Server. They are a bit complex, but basically they let you create a query and treat it as a table to select from. They also allow recursive calls to themselves, which let you do cool things like query hierarchies of data.
This link offers a pretty good explanation of CTEs.
Anyway, CTEs come in handy combined with the ROW_NUMBER() function to add the ability to page data from an existing stored procedure.
The basic idea here is to modify the original query by wrapping it inside of a CTE that adds a RowNumber column.
Then we can select from that query the rows that are in the range we want.
Vegetables are good for you
Let’s look at an example:
Now let’s modify this query to be pageable given we have the first and last record we want. (first record = page number * rows per page, last record = page number + 1 * rows per page)
Okay, so it is actually not that bad.
A couple of notes to help understand what happened here.
- ROW_NUMBER() requires an OVER clause which basically will determine how you calculate what the row number is. In this case, we are going to sort by name. So, the first alphabetical name will be row number 1, and so on.
- We’ve wrapped the query in a CTE and added an additional column so that we have a row number in our new virtual table. (Think of the CTE as creating a temporary view for us.)
- We are selecting everything out of the original query that is in the range of rows we want. (Depending on how you define your range you may use different equality operators. If you range is inclusive, you would do <= and >= , and if it is exclusive it would be < and >. In this case we are both.)
A word on total row count
If you need the total number of rows so that you can pass that back to the pager in your code, then you will probably have to select the results from the CTE into a temporary table and return two result sets from it.
I couldn’t really find a way to select the total rows and the data from a single CTE expression. If you know a way, please let me know.
