I'm developing a small app in classic ASP and I feel that the limitations of ASP will make this more a SQL job than an ASP job. The site is a basic craigslist style ad search site. The catch is that some ads are "preferred" and need to show up at the top of the results, they also need to be randomized so that the most recent preferred ad won't always be the first displayed(to make sure everyone gets their money's worth). However I have paging on my site so when the client clicks to view the next page the query is re-executed and the results re-randomized, problem being the results aren't page 2 of the original results but page 2 of another randomized resultset. Is there a way to run a query (SQL 2005) and cache those results within the database to be called later (possibly tagged by sessionID?)? I've debated using the FOR XML command and saving the results to a session variable but I'm not sure if that makes the most sense. I suppose the answer would be to insert the results in a temp table that has a pre-defined lifespan, but I don't think that's available.
Just thought I'd post my solution as it stands. I ended up with two stored procedures.
The first executes the search and returns the ID's of the retrieved ads in XML to the client (using the FOR XML command). The client stores this information in a session variable.
The second procedure takes the XML data and basically joins it on the other related tables and returns the full data required.
This way I can execute the search and "cache" the results with the client so that the search doesn't have to be re-executed every time the page is refreshed. Point being I can randomize the results once and not have to worry about them being re-randomized each time the client advances through the results.