Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Returning Rows 51 - 100 in as Stored Procedure
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 10/29/2000 :  23:16:10  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Mark writes "I have a web application using SQL Server 7.0 that returns the results of a query page. Since a query could return thousands of rows I only display 50 at a time for speed. The user then hits the next arrow to display rows 51 - 100. Currently I am using ADO with client side curser to implement Absolute Paging. I would like to be able to do all of this in a stored procedure but I have not figured out how to do so using T-Sql. I know about the Top 50 but that doesn't help me with the next 50 or the next 50 after that. I have stumped several DBAs with maybe you all can help."

graz
Chief SQLTeam Crack Dealer

USA
4149 Posts

Posted - 10/29/2000 :  23:16:10  Show Profile  Visit graz's Homepage  Reply with Quote
Actually I already wrote an article about this called What's after TOP. You can find it here: http://www.sqlteam.com/item.asp?ItemID=566
Go to Top of Page

paulmelba
Starting Member

8 Posts

Posted - 05/15/2007 :  15:47:08  Show Profile  Visit paulmelba's Homepage  Click to see paulmelba's MSN Messenger address  Reply with Quote
SPAM DELETED

Edited by - jsmith8858 on 05/15/2007 16:05:48
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
651 Posts

Posted - 05/15/2007 :  20:01:01  Show Profile  Reply with Quote
Since you didn't mention a specific table name, here's something that you can play with... and it's very fast in the presence of the correct indexes... first page of a 1.2 million row table in <16 milliseconds... last page in less than half a second...

declare @start datetime
set @start = getdate()
--===== Declare the local variables
DECLARE @PageSize INT --How many rows to appear per page
DECLARE @PageNum  INT --What page number to appear
DECLARE @Skip     INT --Working variable for where to start for page
DECLARE @SQL      VARCHAR(8000) --Holds dynamic SQL
 
--===== Set the local variables for pagesize and page
     -- PageSize and PageNum would be parameters in a stored proc
    SET @PageSize = 50
    SET @PageNum  = 4000
    SET @Skip    = @PageSize*@PageNum
 
--===== Create the dynamic SQL statement that will produce the page
SET @SQL =
'
 SELECT c.*
   FROM dbo.Customer c,
        (
         SELECT TOP '+STR(@PageSize)+ ' * 
           FROM dbo.Customer WITH (NOLOCK)
          WHERE CustID NOT IN (SELECT TOP '+STR(@Skip)+' CustID 
                                 FROM dbo.Customer
                                ORDER BY CustID)
          ORDER BY CustID
        ) d
  WHERE c.CustID = d.CustID
'
 
--===== Produce the page
   EXEC (@SQL)

print CONVERT(varchar(30),getdate()-@start,114)


--Jeff Moden
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000