I tried to get something like this working a little while ago. The problem
that I ran into was that to make this truly scaleable I needed to create an
sp that generated a SQL string each time, which removed a lot of the
advantages of using a stored procedure. It seems that you can't use a
variable to set how many records you want to return (ie SELECT TOP @Var).
For our purposes (ASP pages and not too many records) it was easier to
return the whole lot and move to the correct position. I've included a bit
of an example of what I was looking at below:

CREATE PROCEDURE qryPages
@Start Int, @Records Int
AS

SET NOCOUNT ON
DECLARE @SQL VarChar(200)

SET @SQL = 'SELECT MyField FROM (SELECT TOP ' + CAST(@Records AS VarChar) + 
    ' FROM (SELECT TOP ' + CAST((@Start + @Records) AS VarChar) + 
    ' MyField FROM tblData ORDER BY MyField ASC) ORDER BY MyField DESC)' +
    ' ORDER BY MyField Asc'

EXECUTE(@SQL)

regards
David Cameron
nOw.b2b
dcameron@i...

-----Original Message-----
From: Roland Boorman [mailto:r_boorman@y...]
Sent: Wednesday, 27 June 2001 12:36 AM
To: sql language
Subject: [sql_language] Re: Top query - next page



 There has been much discussion about pages and getting specific records
from SQL.

In this case I would suggest a embedd selects that reverses the record order


of the inner query by using DESC and an outer select that reverse the result

back.

Select * from (Select Top 100 * from (Select Top 200 * from table 
order by field) order by field DESC) order by field

 

  Giedrius <fozis@c...> wrote: Hello, 

If "Select Top 100 * from table order by field" will return top 100 
records, how to make query to get second page, i.e. records from 101 
till 200?

Thanks
Giedrius