Re-ordering SP results by column

[ranta]

I am currently producing a number of reports in ASP built via stored procedures. Basicaly, each stored procedure will build a temp table of results dependant upon the parms passed from the ASP page. I then return these results to the ASP page and format the results to get the report to look how I want it.

Want I want to be able to do is allow the user to click on any one of the column headings on the ASP results page and to get the report to re-order by this column.

Obviously I could pass this info back to the Stored Procedure and re-run the report but many of these reports are quite time consuming. I would rather just allow the user to be able to play with the results without having to re-run the report....

Does anyone out there have any ideas on how to do this, I have thought that maybe I could load all the results into a mulit-dimensional array within the ASP and re-sort this array by the column selected (not really sure how realistic this is). I was also wandering if there was anyway that you can make the temp table within the stored proc persistant, so that I could re-call the stored proc and the results would still be there, that way I could just re-order them....?

Any help on this one would be much appreciated.

Thanks,

Ranta

 

[iker3000]

Hi!

How about using a permanent table, which has a column like report_ID.
If somebody asks for a report, you generate a random report_ID and call the stored procedure wiht the report_ID as parameter. The sp stores the data in the permanent table (storing report_ID in the given column). If you want to order columns differently, you can just select from the permanent table with report ID as restriction.
(don't forget to empty the table sometimes)

IKER

 

[SQLSister]

A permanent table is a bad idea if you have multiple users. I would suggest you send the temp table to the clinet and handle things like re-ordering on the columns at will there. This will reduce the load of both the web server and the SQL server by reducing the number of trips. As to how to do that on the client end, you probably need to ask ina asp forum. We do this sort of thing all the time in ASP.net but I don't know if you can do it in classic ASP.

Incidentally why are you creating temp tables to get your report? You can return a plain old select statement to the client. YOu aren't using cursors are you? If so you will need to redo the stored procedures to use a set-based statement or your web site will be really slow!

 

[iker3000]

A permanent table is a bad idea if you have many users (many-many at the same time) and many records. However the above given example can handle some users (with some thousend of records/report) with ONE permanent table without any problem, and you don't have to mess around with ASP.

IKER

 

[ranta]

Thanks for that guys but a perm table is of no use. I am using temp tables to collate the info I need within the SP, many of these reports are quite complicated and I am unable to get all the data in the format I need simply by using SQL Select statements.

As all these reports have different info and different columns, some of these columns are built on the fly, for example the user is able to select a date range and the report will produce data for each month within this date range. These columns are built on the fly within the stored procedure.. this would obviously not work with a perm table.

Basically I need the temp table which is built on the fly in the SP to be availably after the SP has closed, either that or find someway of keeping the SP open for the duration...