Paged results from SQL Query?

I have been searching this topic on and for quite some time and can't seem to find a decent answer. Is it feasible to do your paging strictly from a SQL query? Pass the query the pagesize, what page to return and what to sort by?

Hi,

If you are talking about datagrid customer paging, here is a solution I read a few weeks ago.

It is through a SP with dynamic sql statement. Following that article, a user gave another approach without dynamanic sql for a known table. Here is the web link for that article at eggheadcafe.com.

"Custom DataGrid Paging at the Server" by Dr.Bromberg

http://www.eggheadcafe.com/articles/20060109.asp

FYI

If there is not what you are looking for, please post back. ( I don't know whether I inderstand your question correctly about "paging strictly from a SQL query?" )

Regards,

Limno

|||

If you are using 2005 you can, a new ROW_NUMBER() function has been added so you could potentially use it to get rows say from 10 to 20. I have to say I have never used it, but after a quick google:

SELECT Description, Date
FROM (SELECT ROW_NUMBER() OVER (ORDER BY Date DESC)
AS Row, Description, Date FROM LOG)
AS LogWithRowNumbers
WHERE Row>=1 AND Row<=10

I dont know how flexible it is I have to say, but hope that helps!

W

|||That was pretty much exactly what I was looking for. I want to avoid pulling 1000's of results when the user may only be looking at 20 or 30 of them. The other discussions I have read involve building a sorted temporary table with an autonumber column then pulling the page of data from there using < and > on the numbered column. I am concerned that this approach would be bad for performance. The link you have given seems like a very good solution!|||It would be even better if someone could find out how to parameterize the ORDER BY clause, so you can enable sorting on a multi-column table. I've been trying to figure it out for a couple of days, but the CASE syntax breaks down if the columns have different data types...