CODEWITHSUNDEEP
c#sql
mo alaz
mo alaz

Reputation: 4769

SQL Parameter with multiple values

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?

Upvotes: 1

Views: 21521

Answers (3)

Richard Newman
Richard Newman

Reputation: 630

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.

Upvotes: 0

Brady Holt
Brady Holt

Reputation: 2944

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

Upvotes: 1

Palin Revno
Palin Revno

Reputation: 586

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.

Upvotes: 0

Related Questions