Sorting and Paging Recordsets in SQL Server

re: Sorting and Paging Recordsets in SQL Server

PaulWilson — Fri, 20 Apr 2007 20:48:43 GMT

This was written long before Sql 2005 was around with the RowNumber function. My ORMapper does support using the RowNumber function with Sql 2005 for optimal paging.

re: Sorting and Paging Recordsets in SQL Server

Olavo Neto — Fri, 20 Apr 2007 19:47:22 GMT

Have you ever consider this?

http://aspnet.4guysfromrolla.com/articles/032206-1.aspx

re: Sorting and Paging Recordsets in SQL Server

Jacqueline — Thu, 23 Nov 2006 03:29:32 GMT

I found that this did not work when performing a descending sort on the second page of a multipage recordset. However adding an additional parameter for sort order and only calling it on the outermost sql statement fixed the issue.

PageCount

Daniel Martinez — Tue, 01 Aug 2006 16:57:35 GMT

This is a great SP and just what I was looking for. I know this is a dumb question...I don't understand how to get the recordset pagecount in ASP. I see the pagecount being returned when I execute the SP in a new query...

re: Sorting and Paging Recordsets in SQL Server

Resa — Mon, 02 Aug 2004 06:06:00 GMT

I'm using sql server, and the NextResult method doesn't work, is there another way, i mean using sql server? thanks

re: Sorting and Paging Recordsets in SQL Server

Paul Wilson — Tue, 27 Jul 2004 09:59:00 GMT

Assuming you're using .NET and a DataReader then the NextResult method is what you want. If you are using a .NET DataSet then you will look at the second table, i.e. .Tables[1].

Taking the value of PageCount

Resa — Tue, 27 Jul 2004 05:59:00 GMT

Excuse me but i have never used a stored procedure before, and although your code works great (thanks a lot by the way :)) i want to ask how can i get the PageCount value from the second table generated by ur stored procedure? Thank you very much in advance :)

re: Sorting and Paging Recordsets in SQL Server

Jonny — Wed, 21 Jul 2004 11:29:00 GMT

I believe I read somewhere the sp_executesql will compile the dynamic query and create an execution plan for it. This may speed this up a bit if the same exact query is executed.

re: Sorting and Paging Recordsets in SQL Server

Paul Wilson — Thu, 08 Jul 2004 13:42:00 GMT

I actually just got done reading http://codeproject.com/aspnet/paginglarge.asp -- and I may switch what I use. I've never heard any complaints, but it does look like there are better solutions.

re: Sorting and Paging Recordsets in SQL Server

senkwe — Thu, 08 Jul 2004 13:34:00 GMT

Has anybody tried this for tables with a few million rows yet? Thanks.