In my ASP page I have a record set that joins many tables. I want the user to be able to select what field to sort on. I cannot just update the "ORDER BY" clause in the select statement with the name of the sorting field though(would take too long too explain why!). What I need basically is to be able to retrieve the record IDs in a particular order. I have a couple of ideas of how to get around the problem but I am not sure whether they are possible to do.
My first idea is to create a temp table within a SQL stored procedure. This would store a records ID number and the order that it should be displayed. In my ASP page I would then include a JOIN to this temp table in the SELECT statement and "ORDER BY TempTable.Order".
My problem with this is that I do not know how, or even if, I can access the temp table from an ASP page. So any advice on how to do this would be appreciated. (If I can get over this hurdle I know it will give me the result I need - I have tried using a permanent table to test the theory of what I need. With different users selecting different fields to sort on however a permanent table is not good!)
The second idea I had is a bit more scrappy. I could retrieve the order in which I wanted to pull out the IDs as a string e.g. '3','51','14' etc and then... (this could be a really stupid suggestion, it is certainly something I have never come across before) some how use this string in the "ORDER BY" clause as a sort of sequence of retrieval.
Any help appreciated, either on these ideas or alternatives.
My first idea is to create a temp table within a SQL stored procedure. This would store a records ID number and the order that it should be displayed. In my ASP page I would then include a JOIN to this temp table in the SELECT statement and "ORDER BY TempTable.Order".
My problem with this is that I do not know how, or even if, I can access the temp table from an ASP page. So any advice on how to do this would be appreciated. (If I can get over this hurdle I know it will give me the result I need - I have tried using a permanent table to test the theory of what I need. With different users selecting different fields to sort on however a permanent table is not good!)
The second idea I had is a bit more scrappy. I could retrieve the order in which I wanted to pull out the IDs as a string e.g. '3','51','14' etc and then... (this could be a really stupid suggestion, it is certainly something I have never come across before) some how use this string in the "ORDER BY" clause as a sort of sequence of retrieval.
Any help appreciated, either on these ideas or alternatives.