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

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

Ask SQLTeam Question

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."

Chief SQLTeam Crack Dealer

4144 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

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

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

Jeff Moden
Aged Yak Warrior

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
   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  
 New 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.11 seconds. Powered By: Snitz Forums 2000