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 |
|
DaveBF
Yak Posting Veteran
89 Posts |
 Posted - 2014-09-02 : 13:23:00
|
I am doing parameterized queries from Visual Basic in Visual Studio. I have a query where I want to do something like this.Select * from people where city in (<list of cities>) The problem is I want to build my <list of cities> in Visual Basic and pass it to the SQL as a parameter.I know I can't do this:Select * from people where city in (@ListOfCities) Currently, I'm doing this by writing the <list of cities> out to a separate table, just so I can do the query.Select * from people where city in (Select CityName from CityTable) Is there a better way? Thanks. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-09-02 : 14:11:14
|
One method would be to split the comma-delimited string into individual pieces, put them into a (virtual) table and then join with that table (or use the IN clause as you are doing). There are string splitters that you can copy from the web e.g. here: http://www.sqlservercentral.com/articles/Tally+Table/72993/ Another quick method, which may not be very efficient if you have large amounts of data is as follows:SELECT * FROM People WHERE ','+@ListOfCities+',' LIKE '%,'+City+',%'; |
 |
|
|
DaveBF
Yak Posting Veteran
89 Posts |
Posted - 2014-09-02 : 15:30:10
|
quote:
Originally posted by James K
One method would be to split the comma-delimited string into individual pieces, put them into a (virtual) table and then join with that table (or use the IN clause as you are doing). There are string splitters that you can copy from the web e.g. here: http://www.sqlservercentral.com/articles/Tally+Table/72993/ Another quick method, which may not be very efficient if you have large amounts of data is as follows:SELECT * FROM People WHERE ','+@ListOfCities+',' LIKE '%,'+City+',%';
Thanks for the reply. The second method you mentioned worked, but as you said, it's extremely slow. I'm trying to understand the first method. I'll look at the link. As you know it's a very long article. |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-09-03 : 12:06:08
|
Even though the article is long, it is simple to use. Copy the function in Figure 21 of that article, paste it to a query window and run it. That will create a function called dbo.DelimitedSplit8K in your database. Then, use it like this:[code]SELECT a.*FROM People a INNER JOIN dbo.DelimitedSplit8K(@ListOfCities,',') b ON a.city = b.item; |
|
|
|
DaveBF
Yak Posting Veteran
89 Posts |
Posted - 2014-09-03 : 13:17:43
|
| This is working!Thank you very much for summarizing the article for me! |
|
|
|
|
|
|
|
|
Parameterized query for a List of Items