| Author |
Topic |
|
Gyto
Posting Yak Master
144 Posts |
 Posted - 2007-09-11 : 11:37:02
|
Hi there,I am getting a headache trying to research what to do when you have a large number of parameters to include in a query. For example, if I have a large number of checkboxes for the user to pick criteria for a report and they select several, I'm assuming it would be bad practise to say:WHERE Field = "a" OR Field = "b" OR Field = "c" OR Field = "d" OR Field = "e" OR.....etc etc etc Is there a good solution for this, given that the number of parameters may vary dramatically depending on what the user selects to include in a report?!I'm running SQL Server 2000 with an ASP front end.Any help would be greatly appreciated!Thanks in advance!Matt |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Gyto
Posting Yak Master
144 Posts |
Posted - 2007-09-11 : 12:20:01
|
| Argh....all looks a bit complicated by my standards....lolI'll see if I can make sense of it....*gets out the coffee*Thanks! |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-09-11 : 12:24:36
|
| Gyto -- why don't you step back a little and show us what you are doing. Are you using stored procedures? Or trying to dynamically build a SQL string in your ASP code?- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
Gyto
Posting Yak Master
144 Posts |
Posted - 2007-09-12 : 05:02:33
|
| Well, to be honest, I am willing to do either if you have any recommendations as to which would be best?I currently have the SQL in the ASP page but it only deals with a set, small number of parameters so it needs to be changed as the user wants the ability to select randomly from a large number of parameters (most likely in the form of a series of checkboxes on the page). It wouldn't take too much effort to move to a SP if that'd be better?! I'm basically just not sure how to write the SQL....here's an example of what I'm trying to do:1) An ASP page with a number of checkboxes for the user to select randomly.2) An SQL statement to take the parameters they choose and perform a select statement on the database accordingly, regardless of whether they choose 1 parameter or 50 parameters.So, firstly I need to ascertain which of the checkboxes the user has selected and then perform a select statement using only these parameters......without using numerous 'OR' functions |
|
|
|
Gyto
Posting Yak Master
144 Posts |
Posted - 2007-09-12 : 11:13:10
|
Ok here's what I've done which works ok.....1) Collected all the parameters from the checkboxes as selected by the user;2) Concatenated them into a string with each parameter in single quotes seperated by commas, as follows: 'Parameter1', 'Parameter2', 'Parameter3', etc...3) In the SQL statement I used an 'IN' statement along with that string as follows:WHERE Location IN("& LocationString &")I'm guessing this may not be the most efficient way of doing this but it works ok for now.....and it seems to run relatively quickly too, even with over 100 parameters :)....I guess what I've learnt is that my programming and SQL suck, but at least they work.....sometimes.....and if a little inefficiently.....lol |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2007-09-12 : 11:43:18
|
| Gyto, to clarify are you using all the check boxes against one table? For example, say the user can select colors (Red, Blue, Green, Purple, etc..) Do you just need to construct a query for every checked color, like the IN clause you mentioned above?If so, you could build your Dynamic SQL and use the IN clause. Another option would be to use a stored procedure, pass in a separated list (comma, pipe, space, etc), Split that string into a table and join that to you Main table or use it in an IN clause in the stored procedure.If that is the case I’m sure we can help you put something together. I didn’t want to put together a large sample if this doesn’t meet your needs.-Ryan |
|
|
|
Gyto
Posting Yak Master
144 Posts |
Posted - 2007-09-12 : 11:52:10
|
I'm actually using three concatenated strings in a couple of different SQL statements with multiple joins....but the largest statement is as follows:Select vCurrentPosts.LOCATION, ABSDET.DAYPORTION, ABSDET.SELF_CERTFrom ((EMPLOYEE_Table INNER JOIN vCurrentPosts ON Employee_Table.EMPLOY_REF = vCurrentPosts.EMPLOY_REF)INNER JOIN ABSENCE ON Employee_Table.EMPLOY_REF = Absence.EMPLOY_REF)INNER JOIN ABSDET ON Absence.ABSENCE_REF = Absdet.ABSENCE_REFWHERE (((ABSDET.ABS_DATE BETWEEN '"& DateFrom &"' AND '"& DateTo &"')AND ((Absence.UNTILDATE BETWEEN '"& DateFrom &"' AND '"& DateTo &"')OR (Absence.UNTILDATE IS NULL))AND (Absence.ABS_REASON = 'sick')AND (Employee_Table.LEAVER = 0)AND (vCurrentPosts.LOCATION LIKE '%"& LocationList.Fields.Item("Location").Value &"%')AND (vCurrentPosts.DEPT IN("& DepString &"))))This example only uses one of the concatenated strings though, which is 'DepString'. |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2007-09-12 : 11:59:17
|
| The best way to accomplish this IMO is to do what Lamprey said and take that comma delimited string and parse it out and put each value into a #table to use in your select with a join rather than an IN statement or using any kind of dynamic SQL. You can write an SP or function that you pass in the delimiter (',' in this case) and the @string of values and have the SP or function parse those into a table with muitiple rows. That way anytime in the furture that you have this come up, you can just call the SP or function to return you a table with the string parsed into it to use in your join.As an example, a company I used to work for had their own in house developed security framework for their .net apps. Different SPs would get called multiple times in the app (when the user clicked a link) to see what their access should be and populate the page(s) and all. At some point it was brought to my attention that it was taking a long time to run. After examining it (in Prod) I found it was taking 7-8 seconds to run each time. So a user clicks a link in the app, waits 7-8 secs, clicks another link, waits 7-8 secs, and so on. I found that the SP had been written with dynamic SQL. It was passed a @string with say 50 values seperated by a comma and then that @string was put into dynamic SQL with an IN. I wrote a seperate SP to take a @string and delimeter and put the @string into a #table with one row per comma seperated value. I then coded their SP to call my parse SP and pass the @string to it. Then they could use the #table in their final select with a join instead of dynamic SQL with an IN statement. Their SP went from taking 7-8 secs to run to taking less that .1 secs to run...thus making the .net app run much faster for the end users. |
|
|
|
Gyto
Posting Yak Master
144 Posts |
Posted - 2007-09-13 : 05:23:18
|
| Great thanks for the advice all :) |
|
|
|
|
Dealing with large numbers of parameters