June 6, 2003 at 7:01 am
Does anyone have a suggestion on how to handle ASP paging using an XML DOM? I'm looking for a server-side solution, so using a data-island is not the answer I'm looking for.
What I would like to do is to be able to use a SQL sproc and build the paging meta data inside. To do so requires me to select and order all the active rows in a table and number the rows. Then I can select rows x through y using XML explicit.
Currently I'm using a select into, with an order by statement and adding and identity column to count the rows. I know this is not guaranteed to work 100% of the time. Right now my data set is under 5000 and it's working, but I'm looking for a more bullet-proof solution.
Thanks!
Linda Boumarari
GTN Inc.
June 6, 2003 at 7:24 am
The way I do it and have yet to have a probem is quite simple.
Run your select, adding the results into a temp table (Rather create the table instead of using SELECT INTO)
If the user wants the first 10 rows, SET ROCOUNT 10
Select all columns from #temp.
If 10-20 rows are wanted, SET ROCOUNT 10 and DELETE FROM #Temp
Set ROW COUNT 10, Select all colls from #Temp.
etc etc.
If the record set is large, this could slow down. I have used it on 100000 odd rows at it got a bit slow / intense.
What I dened up do was selecting the PK out of the temp table into a second temp table. Deleting the nessasary number of rows the second table and then selecting the PK from T2 out joining onto the first temp table.
This caused SQL to delete less data (Only the PK of temp 2) which caused it to run much faster.
Hope this helps!
Cheers,
Crispin
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
June 6, 2003 at 8:45 am
Crappy
Why don't you add an identity column to your temp table and then select between key values
ie
create table #temp (id_no identity (1,1), val varchar(50))
<insert your data>
for first ten
select val from #temp where id_no between 1 and 10
for next ten
select val from #temp where id_no between 11 and 20
etc.
Make id_no a key and roberts your fathers brother.
June 6, 2003 at 9:45 am
Well I started out with a create table and insert and moved to a select into. The issue I have with either solution is that I need my data ordered as well as numbered. I used an order by in both scenarios. However in SQL 2000, my understanding is that the order by statement isn't guaranteed to work in an insert or select into situation. It hasn't happened yet, but my row numbers could be off and my data won't be sorted in any meaningful manner.
June 9, 2003 at 3:07 am
Do you remember where you read that order by might not work?
I realise that you are pointing out that it is in 'insert' or 'select into' but it sounds like rubbish to me. I've never known 'order by' not to work, and if you know anyone who has then I would suggest they are *special* in a very special way.
I could be wrong, but I would be delighted to know your source for this information if I am.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply