How do you you get "n" results at at time?

[Waiman]

I'm trying to write a Stored Procedure to return "n" number of results, from a undetermined number of rows. I intend to use it for discussionboard like view, in that you have links for "n"th page of "y" records.
e.g.

[1], 2, 3, 4, Last Page



The problem I'm having is that the table doesn't have an ordered index column (e.g. 1,2,3,4,5,6,... Instead its more like 1,2,5,9,10,43,...) so I can't do "BETWEEN X AND Y".

I've tried using a temporary table in the stored procedure, but ASP gives me an error becasue the Stored Procedure returns a closed recordset (I don't know exactly why - But I think it's because the first select statement in the Stored Procedure is an INSERT for the temporary table.)

The only other alternative is to do the processing in ASP, using GETROWS to put the entire recordset into an Array and using the array index to get the required rows. But seems such a inefficient way of doing it, as I have to return every record before I can find out which one's I want.

Anyone with any pointers?
Many thanks.

 

[KrisSimonis]

Say our table looks like this:
Table A
id: info:
1 bla...
3 bla...
4 bla...
8
10
11
12
16
19
20
23

when the list is first accessed, do this:
SELECT id from A order by id
now, your page knows howmany records it wants to display at a time so it will know at which record it should cut off and for every page it generates the link for, it knows which rownumber it should have.
Say we display 4 items at a time, then pages should contain the following records:
1: 1, 3, 4, 8
2: 10, 11, 12, 16
3: 19, 20, 23

set up your pages that you use a larger frame that does this
then for the actual page with data give the cutoff points as parameters.
Page 1 in the frame will get 1 and 8
Page 2 will get 10 and 16
and page 3 will get 19 and 23
This page then creates the query that will actually get your data aka:
x = 1
y = 8
( from the master page )
SELECT id, info FROM A WHERE id BETWEEN(x,y)
send this to the database and display the result set.
And presto.

 

[tlbroadbent]

Check the following link for some paging code. It may help you debug or redesign yours.

http://www.aspfaqs.com/webtech/062899-1.shtml

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[Waiman]

LOL! I feel such a fool! (I am rather new to SQL)

Many thanks to tlbroadbent and SuicideCommando for the help.

I've just learnt what "SET NOCOUNT ON" does. By setting this NOCOUNT to ON, it stops any results being sent back to ASP, therefore no more closed record sets.

Now I can use a temporary table with a proper ordered index to get the required rows.