From your example I can see that you are using an array of int not a
list of string. It would be safe to construct a parameter as string in your code and pass it to the SP as a variable since you are using
int.
was just an example... the list could be:('A34', 'C254', '30', '20', '40')ormore...
I don't want to be dissrespectfull but I am afraid it was a bad example and this one is much much better. This is an
array of string and dynamic sql just got kicked out due to security. You will have to use one of the alternatives. I don't even know what version of SQL you are using. I also don't know how much experience/knowledge you have with C# and Sql.
You can pick any other method I suggested that is described in the links I've posted above. I also don't know how you designed your application. Do you use EF, ADO, ...? Can you please try and implement one of the methods and reply with a specific error, question?
Please "Mark As Answer" if the post helped you.
mitja.gti | www.mitjagti.com
You could use dynamic sql to execute In clause. Please follow below query to modify your query.
declare @par varchar(max)
SELECT @par = Coalesce(@par + ''',''','') + cast(number as varchar(10))
FROM Your_table where <condition>...
set @par = ''''+@par+''''
select @par
declare @string varchar(max)
set @string = 'SELECT * FROM Product WHERE name in ('+@par+')'
exec (@string)
Thanks.
Please mark the replies as answers if they help or unmark if not.
Feedback to us
dupin
Member
219 Points
203 Posts
select passing a list of strings
Aug 19, 2012 12:06 PM|LINK
I would like to perform a select on a table by passing a list of strings.
Example: SELECT * FROM Product WHERE name IN ('10 ', '20', '11 ', '18')
This list can change from time to time: a time 10 elements, once 20 and so on...
I tried
"SELECT * FROM Product WHERE name IN (@ par)"
then setting parameter
"cmd.Parameters.AddWithValue ("@par", listname)"
but does not work!
I use C #
thanks a lot
mitja.GTI
Star
11157 Points
2094 Posts
Re: select passing a list of strings
Aug 19, 2012 01:57 PM|LINK
From your example I can see that you are using an array of int not a list of string. It would be safe to construct a parameter as string in your code and pass it to the SP as a variable since you are using int.
Also please check this post How to pass a list of values or array to SQL Server stored procedure? and Table-Valued Parameters.
mitja.gti | www.mitjagti.com
dupin
Member
219 Points
203 Posts
Re: select passing a list of strings
Aug 19, 2012 02:01 PM|LINK
Hi,
was just an example...
the list could be: ('A34', 'C254', '30', '20', '40') or more...
however, in database is a field strings..
thanks
mitja.GTI
Star
11157 Points
2094 Posts
Re: select passing a list of strings
Aug 19, 2012 02:14 PM|LINK
I don't want to be dissrespectfull but I am afraid it was a bad example and this one is much much better. This is an array of string and dynamic sql just got kicked out due to security. You will have to use one of the alternatives. I don't even know what version of SQL you are using. I also don't know how much experience/knowledge you have with C# and Sql. You can pick any other method I suggested that is described in the links I've posted above. I also don't know how you designed your application. Do you use EF, ADO, ...? Can you please try and implement one of the methods and reply with a specific error, question?
mitja.gti | www.mitjagti.com
dupin
Member
219 Points
203 Posts
Re: select passing a list of strings
Aug 19, 2012 02:29 PM|LINK
Hi,
no matter if it is a bad example... however, I put the apices...
in the apices are always strings...
I use c#4.0, sqlserver 2008...
now I'm trying to understand the link you gave me..
thanks
Chen Yu - MS...
All-Star
21829 Points
2513 Posts
Microsoft
Re: select passing a list of strings
Aug 22, 2012 07:59 AM|LINK
Hi dupin,
You could use dynamic sql to execute In clause. Please follow below query to modify your query.
declare @par varchar(max) SELECT @par = Coalesce(@par + ''',''','') + cast(number as varchar(10)) FROM Your_table where <condition>... set @par = ''''+@par+'''' select @par declare @string varchar(max) set @string = 'SELECT * FROM Product WHERE name in ('+@par+')' exec (@string)Thanks.
Feedback to us
Develop and promote your apps in Windows Store