OK, I wrote this article back in 2007. From an ASP.NET standpoint it's a bit dated, and if I were to write it from scratch today I could optimize a few things - but the concepts are still valid.
http://www.codemag.com/Article/0703041If you take a look at Figures 1 through 3 on the page, hopefully you can see the intended example - one that pages result sets. It uses the ROW_NUMBER capability that was added in SQL 2005, along with a page number and offset functionality to grab X number of rows at a time, based on the current "page" and sort order.
In SQL 2012 they actually added a new OFFSET/FETCH feature into the language, to support paging more directly. But it was still possible in SQL 2005.
So take a look at the example - I think you might be able to lift a few features from it. Let me know if I can be of any help.
