Need help with creating dynamic SQL statement

[James Shao]

Hi I am trying to create a stock scanner for my website. The user would choose and add certain criterias one by one, and a dynamic SQL statement would need to be created on the fly to retrieve the data. Currently I am able to do this by appending pieces of SQL string after a long SQL string like the following: Whenever "Add Criteria" button is pressed: newQuery = "AND criteria > x" userCriteria = userCriteria + newQuery When "Retrieve Information" button is pressed: Querystring = "Select * WHERE Date = date " & userCriteria Is there a more efficient mechanism? If not that's also fine as it currently works well, but I just need some reassurance from experts. :) Thanks!

modified on Monday, December 21, 2009 12:07 AM

[Mycroft Holmes]

If it works then you can only improve the structure of the query otherwise it is an exercise in string concatenation. If the where clause is the only dynamic piece and the elements are known you can use something like

Where (IsNull(@AttrID, -1) = -1 OR AttrID = @AttrID)

where the default value is null or -1. Caveat, too many of these (about 6 IIRC) MAY affect the query performance I believe. I use it all the time and have had up to 8 elements in the where clause and it worked fine. The only benefit is it is not dynamic, not a huge issue!

Never underestimate the power of human stupidity RAH