You can do pagination by using the standard ROW_NUMBER() function and using CTEs, check my blog post for the same: http://sqlwithmanoj.wordpress.com/2011/12/30/creating-stored...

Also in SQL 2012 now you can use the offset-fetch clause to implement paging, check this: http://sqlwithmanoj.wordpress.com/2011/12/30/creating-stored...