SQL Parameter with multiple values

Question

I am currently passing an SQL Parameter with one value.

So right now I have :

 SqlParameter sqlParameter = new SqlParameter("@Parameter", SqlDbType.VarChar);
 sqlParameter.Value = ParameterValue

and this is working fine in my WHERE clause in my SQL query.

However now I want to leave the option to have multiple values passed in my WHERE.

Instead of passing a regular string, I was thinking of passing a string with commas to separate the values.

So SqlParameter.Value = "value1, value2, value3";

and I want it to act like

WHERE Parameter = value1 OR Parameter = value2 OR Parameter = value3

Is there an easy way to do this where I dont actually have to modify my SQL query?

short answer: no - you will want to pass in multiple parameters (unless you just pass in a string and use a `where Parameter in (v1,v2)` query - but that opens you up to SQL injection — BrokenGlass, Aug 29 '13 at 14:18
See [Parameterizing an SQL IN clause?](http://stackoverflow.com/questions/337704/parameterizing-an-sql-in-clause?lq=1) — Alex K., Aug 29 '13 at 14:20
Solution http://stackoverflow.com/questions/337704/parameterizing-an-sql-in-clause Not the acccepted solution, look at the one with more upvotes. — Daniel Moses, Aug 29 '13 at 14:21
requires change to SQL but you could do with an IN clause perhaps: `WHERE Parameter IN(@Parameter)` — Tanner, Aug 29 '13 at 14:21
possible duplicate of [Passing a varchar full of comma delimited values to a SQL Server IN function](http://stackoverflow.com/questions/878833/passing-a-varchar-full-of-comma-delimited-values-to-a-sql-server-in-function) — Dhaval, Aug 29 '13 at 14:36
It is possible, you just need to create a split table value, there are dozens of samples here or google and change your procedure. The question is why doing so? I would only see this if you were thinking in using N values, but based in your code this is not the case. If you have an specific number of values, use and specific number of parameters. — Gonzix, Aug 29 '13 at 14:57

score 1 · Answer 1 · edited May 23 '17 at 12:05

Bottom line: you're going to have to change either the SQL Statement or Stored Procedure to support what you are trying to do.

There are many different approaches to do what you are trying to accomplish but none are ideal, in my opinion. Erland Sommarskog wrote a great article explaining the many ways to pass in arrays and lists to SQL Server (http://www.sommarskog.se/arrays-in-sql-2005.html) which I recommend reading. A clean approach, if you are using SQL Server 2008 or greater, is using Table Valued Parameters (http://www.sommarskog.se/arrays-in-sql-2008.html). With this approach, you are basically passing an array of values into SQL Server.

If you go the Table Valued Parameters approach, your parameter will behave like a table where you can SELECT values from. So, for instance, you might modify your Stored Procedure (or SQL Statement) like so:

 CREATE PROCEDURE get_products @Parameter myCustomParameterType READONLY AS
 SELECT p.ProductID, p.ProductName
 FROM   Products p
 WHERE  p.ProductID IN (SELECT n FROM @Parameter)

There is another SO question/answer which provides more detail on this approach here: How to pass table value parameters to stored procedure from .net code

More info on Table Valued Parameters can be found here: http://msdn.microsoft.com/en-us/library/bb675163.aspx

score 0 · Answer 2 · answered Aug 29 '13 at 14:21

0

Not if your query is "where parameter = @paramter".

Either change your query to "where parameter in..."

Or get your values into another table/table variable and join them.

answered Aug 29 '13 at 14:21

Pavel Ronin

576
4
11

score 0 · Answer 3 · answered Aug 29 '13 at 14:27

If you want to pass in a comma separated list to check values against you can take the list, split it down and insert it into a temporary table or a table variable and then you can do all the normal table statements such as JOIN, IN, EXISTS.

Here is a good article on how to take a Comma separated string and turn it into a table. http://blogs.msdn.com/b/amitjet/archive/2009/12/11/sql-server-comma-separated-string-to-table.aspx

SQL Parameter with multiple values

3 Answers3

Linked