Reputation: 4769
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: 21510
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
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
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