A while back I found a stored procedure that someone was using to create pagination resultsets in SQL Server 2000. While the stored procedure was nice, it didn't allow for the "DISTINCT" keyword—which is necessary for some of my queries. There were also a few other issues I didn't like about the stored procedure, so I re-wrote the stored procedure pretty much from scratch.
In order to support the "DISTINCT" keyword, I had to change the inner query to use a derived table. Since this provides a little more overhead, I added a bit parameter "@Distinct" which inserts the "DISTINCT" keyword into the query and uses the derived table.
Also, on the initial page of results, I simply use the TOP keyword. The previous stored proc used an inner query, which did a TOP 0—which means the code did nothing.