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 |
|
vengeance
Starting Member
2 Posts |
 Posted - 2002-06-21 : 04:29:40
|
| Consider the following SQL statement:SELECT * FROM Users WHERE state IN ('texas','california','kentucky')This would return any users who live in texas, california or kentucky.Now, I have a webform with a list box of all the US States. This listbox has multiple-select enabled, so that I can select many states at a time. I also have webcode that, upon submit, will take all the selections of the listbox and form a comma-delimited string of themi.e stateList = "'texas','california','kentucky'"I'd like to be able to pass stateList to a stored procedure and execute a select that will return all users that live in any of those states, just like the hardcoded statement above.SELECT * FROM Users WHERE city IN (@stateList)This is the stored procedure I'm trying to use, and it is not working. I suspect that although the resultant sql statement after the parsing of the @vars is the exact same, for some reason its not valid. Therefore, I'm interested in a way of performing this type of select without having to use dynamic SQL to build up a bunch of WHERE and AND clauses. Also keep in mind that I've simplified this example by removing my use of the COALESCE function, which I'm already using to avoid dynamic sql when searching for multiple criteria that may or may not be provided.Hopefully a guru out there can help me! I'm desperate-- I've expired all my resources to get help on this one! Any help would be much appreciated. |
|
|
vengeance
Starting Member
2 Posts |
Posted - 2002-06-21 : 04:33:52
|
| I forgot to mention: my apologies if this has been answered already. If so, please just forward me to the appropriate article! I've searched all of sqlteam.com, and found many articles CLOSE to the issue, but not close enough for me to derive a solution ;/ Thanks in advance! |
 |
|
|
YellowBug
Aged Yak Warrior
616 Posts |
Posted - 2002-06-21 : 05:27:49
|
| This is what I would do:CREATE function ArrayToTable[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=14185[/url]Call the stored procedure as usual.Change the SELECT statement to use the table variable.select * from Users INNER JOIN dbo.ArrayToTable(@stateList,@delimiter)ON Users.City = FldHTH |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-06-21 : 08:16:49
|
This should work too.select * from users where isnull(charindex(state, @statelist),-1) > 0 This will result in a table or index scan and ignore any index on state. If that unacceptable, YellowBugs method would be better.<O> |
|
|
|
|
|
|
|
|
Gurus: Help me avoid Dynamic SQL with your skills!