Select range of rows

[bsaucer]

I have a stored procedure with a rather complicated select query. Sometimes this query can return several thousand rows.

I want to be able to select a range of rows by number, such as rows numbered 1200 to 1300. I want to pass these two numbers as parameters. Or I could pass the first row number and the number of rows to return.

I'm using SQL 2000.

Another approach is to return all wows to the ASP.Net application and store the rows somewhere. But where do they get stored? I don't want to send several thousand rows of data to the client machine.

[andreasblixt]

In SQL Server 2005 this is rather pain-free using ROW_NUMBER(), but in 2000 there isn't really a great way to do pagination (why, I don't know - it's in other database engines and it's ludicrous to get all rows when you just need a subset). I found this article which might prove useful:
http://www.bdiez.com/tips/sql_2000_pagination.htm

Edit:
This might be more efficient as it doesn't copy the data - only the keys:
http://www.15seconds.com/Issue/010607.htm

And a more thorough walkthrough here:
http://www.4guysfromrolla.com/webtech/042606-1.shtml

[dglienna]

The SP could create a @temp table, usingng rec counter for each record. Then it'd be no problem to pass it along.

[hspc]

This is a very good article about pagin large recordsets:
http://www.codeproject.com/aspnet/PagingLarge.asp

[bsaucer]

I just tried using the temp table. The ASP.NET page returned error messages saying that all the columns were invalid. When I tried it in the query analyzer, there were no rows returned.

The query retrieves data from a view with inner join and left joins to several tables. The view also selects from several tables joined with other tables.

In SQL Server 2005 this is rather pain-free using ROW_NUMBER(), but in 2000 there isn't really a great way to do pagination (why, I don't know - it's in other database engines and it's ludicrous to get all rows when you just need a subset). I found this article which might prove useful:
http://www.bdiez.com/tips/sql_2000_pagination.htm

[bsaucer]

I had found an error in my query the first time...

However, I now get multiple copies of each row that I originally would have gotten, and the total row count (unfiltered by page number) of the output table is now several times the size of the original query.

Also, I don't always get the same rows each time when I filter the results by rows.

[dglienna]

Post your SP