How to return a specified # of rows

[John David]

Trying to do a paging scheme without using #Temp tables in MS SQL 7.0

Client calls a sp passing 1 and sql returns the first 100 records.
Client sends a sp passing 100 and gets the next 100 records.

Process continues till @@fetch_status <> 0 or the client can stop sending requests.

I implemented it easily using fetch absolute into a #temp table but this has dissaster potential in a multiuser environment since everyone will be using this query continously and there is no user limit.

What other options do I have?

Thanks,

John

[Carly]

Why use SP cursors?
From VB,ASP, CC+ or what ever you are using,
Why can&#39;t you create your recordset and use local code to control it?



------------
John David at 10/29/99 7:43:17 PM

Trying to do a paging scheme without using #Temp tables in MS SQL 7.0

Client calls a sp passing 1 and sql returns the first 100 records.
Client sends a sp passing 100 and gets the next 100 records.

Process continues till @@fetch_status <> 0 or the client can stop sending requests.

I implemented it easily using fetch absolute into a #temp table but this has dissaster potential in a multiuser environment since everyone will be using this query continously and there is no user limit.

What other options do I have?

Thanks,

John

[John David]

This recordset would have to contain a few hundred thousand records that could be updated on demand.

We may implement this in future versions but for now I am personally stuck with sql to accomplish it. I don&#39;t want to use cursors anywhere due to their demand on the server but this seems to be the only viable solution at present.

Any other ideas?

Thank you for the response!

~John

------------
Carly at 11/1/99 3:41:02 AM

Why use SP cursors?
From VB,ASP, CC+ or what ever you are using,
Why can&#39;t you create your recordset and use local code to control it?



------------
John David at 10/29/99 7:43:17 PM

Trying to do a paging scheme without using #Temp tables in MS SQL 7.0

Client calls a sp passing 1 and sql returns the first 100 records.
Client sends a sp passing 100 and gets the next 100 records.

Process continues till @@fetch_status <> 0 or the client can stop sending requests.

I implemented it easily using fetch absolute into a #temp table but this has dissaster potential in a multiuser environment since everyone will be using this query continously and there is no user limit.

What other options do I have?

Thanks,

John

[Sharif]

John;
I have been thinking about the same issue myself, and agree about the server load problem, and I have similar idea to what you’ve proposed, but I am not clear about a couple of things:
1 – I guess you are using a cursor, so is this a global cursor that get put in tempdb, or do you construct a table in tempdb and put the data there.
2- is assuming that the use app holds a session to sql server while the user pages through the result, correct; otherwise the cursor or table will be flushed by the server.
If these assumption are true then I have a idea that might help to reduce the load on the server.
Cheers.
Sharif

------------
John David at 11/1/99 12:26:33 PM

This recordset would have to contain a few hundred thousand records that could be updated on demand.

We may implement this in future versions but for now I am personally stuck with sql to accomplish it. I don&#39;t want to use cursors anywhere due to their demand on the server but this seems to be the only viable solution at present.

Any other ideas?

Thank you for the response!

~John

------------
Carly at 11/1/99 3:41:02 AM

Why use SP cursors?
From VB,ASP, CC+ or what ever you are using,
Why can&#39;t you create your recordset and use local code to control it?



------------
John David at 10/29/99 7:43:17 PM

Trying to do a paging scheme without using #Temp tables in MS SQL 7.0

Client calls a sp passing 1 and sql returns the first 100 records.
Client sends a sp passing 100 and gets the next 100 records.

Process continues till @@fetch_status <> 0 or the client can stop sending requests.

I implemented it easily using fetch absolute into a #temp table but this has dissaster potential in a multiuser environment since everyone will be using this query continously and there is no user limit.

What other options do I have?

Thanks,

John