Select range of rows
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 7 of 7

Thread: Select range of rows

  1. #1
    Join Date
    May 2001
    Location
    Mobile, AL.
    Posts
    64

    Select range of rows

    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.

  2. #2
    Join Date
    Jul 2007
    Location
    Sweden
    Posts
    331

    Re: Select range of rows

    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
    Last edited by andreasblixt; July 20th, 2007 at 03:52 PM.

  3. #3
    Join Date
    Jan 2006
    Location
    Chicago, IL
    Posts
    14,874

    Re: Select range of rows

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

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  4. #4
    Join Date
    Apr 2002
    Location
    Egypt
    Posts
    2,209

    Re: Select range of rows

    This is a very good article about pagin large recordsets:
    http://www.codeproject.com/aspnet/PagingLarge.asp
    Hesham A. Amin
    My blog , Articles


    <a rel=https://twitter.com/HeshamAmin" border="0" /> @HeshamAmin

  5. #5
    Join Date
    May 2001
    Location
    Mobile, AL.
    Posts
    64

    Re: Select range of rows

    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

  6. #6
    Join Date
    May 2001
    Location
    Mobile, AL.
    Posts
    64

    Re: Select range of rows

    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.

  7. #7
    Join Date
    Jan 2006
    Location
    Chicago, IL
    Posts
    14,874

    Re: Select range of rows

    Post your SP
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Azure Activities Information Page

Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center