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

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Returning Rows 51 - 100 in as Stored Procedure

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2000-10-29 : 23:16:10
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."

paulmelba
Starting Member

8 Posts

Posted - 2007-05-15 : 15:47:08
SPAM DELETED
Go to Top of Page

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-05-15 : 20:01:01
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
   

- Advertisement -