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
 Can I pass a list of items through a Parameter?

Author  Topic 

DaveBF
Yak Posting Veteran

89 Posts
Posted - 2014-02-12 : 09:29:19
I have a SQL query like this:

Select * from myTable where myTable_ID in (2,6,7,9)


I want to build the list of values that are in parenthesis, in my VB code and pass it in through a parameter, so it's like this:

Select * from myTable where myTable_ID in (@myValues)


Is this possible?
I tried it where myValues = '2,6,7,9' but am getting a conversion error. I'm starting to believe it's not possible to do what I'm trying to do. Is there another way?
Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2014-02-12 : 09:49:58
refer to below
http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
http://msdn.microsoft.com/en-us/library/bb510489%28SQL.100%29.aspx

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

alexsts
Starting Member

13 Posts
Posted - 2014-02-12 : 10:15:23
quote:
Originally posted by khtan

refer to below
http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
http://msdn.microsoft.com/en-us/library/bb510489%28SQL.100%29.aspx

KH
[spoiler]Time is always against us[/spoiler]


Since you did not said which SQL server you are on I will assume that it is at least SQL 2008 or better.
Use User-Defined Table Types and it will work like a charm. You will need to do proper modification on your VB side but it well worse it.
Here is pretty good article how to do this with examples:
http://blog.sqlauthority.com/2008/08/31/sql-server-table-valued-parameters-in-sql-server-2008/
Cheers.

Alex
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site