Unanswered: Stored procedure sort records - possible?
A template ASP landing page uses the following SQL to create a list of properties, 10 at a time, for each landing page. Landing pages are dedicated to a different type of property and location.
We wish to force certain featured properties to be on Page 1 above the remainder of the results (which are sorted by price), but need different properties to be forced depending on the page.
At present we have a field called LANDINGFEATURE which is set as 'Yes', upon which the results are sorted (red text below), but these apply erroneously across all landing pages. So a house in New York may be forced correctly onto Page 1 of the New York landing page, but it will also be forced onto the USA property landing page.
Therefore, is it possible to pass in an identifier to the procedure and to sort properties depending on that identifier (e.g. New York Property) above the rest of the results?
The code I posted is the best I have been able to find and amend and write over a couple of years of trying to find a solution. Our website was built by a developer years ago that used ASP paging which was extremely slow, being that it downloaded thousands of records only to choose 10. The SQL above gets the 10 at the server, and is tens of times faster.
We have dozens of countries, many more regions and many many areas that can be searched on the website and that does not include searches for numbers of bedrooms, land, etc which I have taken out of the example for clarity. Writing CASE statements to include every single permutation would be an enormous nightmare, particularly as new areas are added regularly.
I have never understood how there is not a tried and tested method for retrieving 10 records of varied data from large databases that doesn't involve dynamic SQL. I have asked on countless forums how to do this and been given the same "you can't, or dynamic SQL". I have used parameters in order to hopefully cut off some attacks but, like you say, it's not by any means perfect.
I look at websites like Amazon or large travel sites and think "how the hell do they do it?" as they must have even more permutations of searches and they have fast, probably secure SQL.
Not sure how it will work though, as the types of searches we need to do on our database are very varied and, if I am reading you correctly, I would need dozens of stored procedures.
Users and our pages need to be able to perform searches for properties like this:
USA + Golfing
USA + New York
USA + New York + Manhattan
USA + New York + (Manhattan + Brooklyn)
USA + New York + (Manhatten + Brooklyn) + 3 bedrooms
USA + 3 bedrooms
USA + Golfing + 3 bedrooms
USA + North Carolina + Golfing + 3 bedrooms + 2 bathrooms + Under 1,000,000 USD
All Countries + Golfing + 3 bedrooms
How would I take into consideration all those permutations without dozens of different procedures? Maybe I'm not quite understanding what you mean?
Dorset, there has to be SOME limit to search functionality.
In my experience, management requirements for search features is "Search for anything, anywhere", while 99% of all user searches are MUCH more limited.
Do some research on what the users actually do. Search functionality can become a major coding and security headache if you let it.
A more robust option that might be worth looking into is Microsoft SQL Server's Full-Text search functionality. It is more appropriate for broad-bases searches, but more complex to implement and administer.
If it's not practically useful, then it's practically useless.