Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
julianfraser
Starting Member
19 Posts |
 Posted - 2005-05-09 : 13:21:07
|
| I have a stored procedure that returns a table using a dynamic WHERE claus that is generated by an ASP script. I wan to know if there is a more efficient way to achive this. Also the '&_' that I have put at the end of each line, is this correct, I haven't tried this sproc yet so I don't know.And is the correct way to cancel a quote mark within a string with a backslash? Ie. 'here is \'some\' text'???Here is the sproc...CREATE PROC sproc_search @where_clauseAS DECLARE @sql_string varchar(8000) SELECT @sql_string = 'SELECT p.propertyID, pt.property_type, p.address1, p.address2, p.town, p.county, p.postcode, p.price, p.bedrooms, p.bathrooms ' &_ 'FROM property p ' &_ 'INNER JOIN property_type pt ON pt.property_typeID = p.property_typeID ' &_ 'WHERE ' + @where_clause EXEC (@sql_string)GOHere is an example of the dynamically generated where clause...(property_typeID = 'HDE' OR property_typeID = 'HSD' OR property_typeID = 'HTE') AND (price BETWEEN 70000 AND 450000) AND (bedrooms BETWEEN 0 AND 8) AND (bathrooms BETWEEN 0 AND 8) AND (sale_statusID = 'FSL' OR sale_statusID = 'UOF') AND (property_termID = 'FRE' OR property_termID = 'FSH') AND (propertyID IN (SELECT propertyID FROM LM001_property_property_feature WHERE property_featureID = 'BGR') AND (propertyID IN (SELECT propertyID FROM LM001_property_property_feature WHERE property_featureID = 'GDE') AND (propertyID IN (SELECT propertyID FROM LM001_property_property_feature WHERE property_featureID = 'GUN') AND (propertyID IN (SELECT propertyID FROM LM001_property_property_feature WHERE property_featureID = 'CAB')Thanks,Julian. |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
|
|
A more efficient way to achieve this SELECT?