How to get data page by page and sort column

How to create a SP, using which I can get result page vise. I want to pass pageSize, pageNo, sortCol and direction into SP and want result based on this info. Can I handle this in SP's logic?

Table1 { First, Last, Location }

SP_GetAll(page开发者_StackOverflowNo=3, pageSize=10, sortCol="First", direction="ASC")

Any new feature provided by SQL Server 2008 for this?

You may find an inline table valued function (TVF) more suitable to this than a sproc. Reason being is the results can participate in other queries.

At the beginning of your select statement, include the following:

SELECT  TOP (100) PERCENT ROW_NUMBER() OVER (ORDER BY ...

At the end of your select statement, put the following:

WHERE   (Table.rowNo BETWEEN (@pageNumber - 1) * @pageSize + 1 
         AND @pageNumber * @pageSize)

If the data set is large, (or your query complex), consider spooling the results to a temp table and paging around the temp table.