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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Parameterized query for a List of Items

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+',%';
Go to Top of Page

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.
Go to Top of Page

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;
Go to Top of Page

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!

Go to Top of Page
   

- Advertisement -