Board index » delphi » How can I fetch a range/block of absolute rows from select stmt (for html paging)

How can I fetch a range/block of absolute rows from select stmt (for html paging)

Hello:

MSSQL7
Is there any straight forward means of fetching or returning a block of
absolute records.  Similar to saying "Select Top 20 from aTable..." But
I want to do something like be able to get rows 1-20, or 21-40, 41-60
etc.  I am paging a result table in an HTML page, so I don't have a
constant connection to the db or webserver.  I know I could do it using
the TAdoQuery object but I don't even want to pass all that data to the
appSever since with every call to a new page is a new request to the
webServer I would have to return all rows to the TAdoQuery object for
every page then only build the dynamic html based on the rows which
should be included for the requested page.
  It would be nice to write a storeProc to "page out" any result set for
any table since we build a lot of our Queries on the fly anyway ( e.g
spGetSqlResultByPage(@sqlStmt varchar(1000) , @page int,
@pagesize int).

I was thinking I could do it in a StoredProc but for one thing I don't
seem to be able to set the Order By fields from a variable.  I could
also accomplish what I am looking for using a cursor but since the Sql
portion would have to be a parameter sent to the StoredProc I would have
to be able to assign that variable to a Cursor Variable, or at least to
the cursor definition.

Or I thought I could do a sub-select and add a row_count column to the
inner select and then in the outer select use the BETWEEN operator on
the row_count field.  But with this method I would have to know how to
accumulate the row count for each record on the inner select. I have a
lot of ideas but I keep running into obsticles because of my lack of
MSSQL knowledge.

1.) Is there a way to grab absolute record ranges or blocks from the
results of a select?

2.) Can I assign a variable (passed to the storedProc containing the
Query string)  to a cursor variable?   I know I can execute a query
which is passed in as a varialbe or built within the storedProc, but can
I assign the result to a cursor?

3.) Is there a way to use variables to reference column names in the
ORDER BY clause,  from within the stored proc

 

Re:How can I fetch a range/block of absolute rows from select stmt (for html paging)


Quote
On Sun, 06 Jan 2002 00:33:43 -0800, Tony wrote:

SET ROWCOUNT 20 will allow you to fetch the first 20 records. Save the
key of the last one then re-execute the query with key > ??
The big problem with absolute fetches is if the data set changes then
you'll either miss data or get duplicate data.
Quote
>Hello:

>MSSQL7
>Is there any straight forward means of fetching or returning a block of
>absolute records.  Similar to saying "Select Top 20 from aTable..." But
>I want to do something like be able to get rows 1-20, or 21-40, 41-60
>etc.  I am paging a result table in an HTML page, so I don't have a
>constant connection to the db or webserver.  I know I could do it using
>the TAdoQuery object but I don't even want to pass all that data to the
>appSever since with every call to a new page is a new request to the
>webServer I would have to return all rows to the TAdoQuery object for
>every page then only build the dynamic html based on the rows which
>should be included for the requested page.
>  It would be nice to write a storeProc to "page out" any result set for
>any table since we build a lot of our Queries on the fly anyway ( e.g
>spGetSqlResultByPage(@sqlStmt varchar(1000) , @page int,
>@pagesize int).

>I was thinking I could do it in a StoredProc but for one thing I don't
>seem to be able to set the Order By fields from a variable.  I could
>also accomplish what I am looking for using a cursor but since the Sql
>portion would have to be a parameter sent to the StoredProc I would have
>to be able to assign that variable to a Cursor Variable, or at least to
>the cursor definition.

>Or I thought I could do a sub-select and add a row_count column to the
>inner select and then in the outer select use the BETWEEN operator on
>the row_count field.  But with this method I would have to know how to
>accumulate the row count for each record on the inner select. I have a
>lot of ideas but I keep running into obsticles because of my lack of
>MSSQL knowledge.

>1.) Is there a way to grab absolute record ranges or blocks from the
>results of a select?

>2.) Can I assign a variable (passed to the storedProc containing the
>Query string)  to a cursor variable?   I know I can execute a query
>which is passed in as a varialbe or built within the storedProc, but can
>I assign the result to a cursor?

>3.) Is there a way to use variables to reference column names in the
>ORDER BY clause,  from within the stored proc

Other Threads