CODEWITHSUNDEEP
sql-server-2008
user1237131
user1237131

Reputation: 1863

How to pass multiple parameters of same column to SP in sqlserver 2008?

MY Table:

id address tag
1  test   class1
2  test1  class2
3  test3  class3

in SP i wrote the select statement like this:

 Select address from mytable where tag IN (@SingleParameter)

Passing parameters to SP from UI as:

string SingleParameter = "";
            SingleParameter = "class1,class2,class3";
            comand.Parameters.AddWithValue("@SingleParameter",SingleParameter);

Then not getting any results if records are there in table.

While sending single value like 

string SingleParameter = "";
            SingleParameter = "class1";
            comand.Parameters.AddWithValue("@SingleParameter",SingleParameter);

then getting results. How to Pass multiple values in string to SP? Please tell me........

Upvotes: 2

Views: 792

Answers (1)

Dmitry Reznik
Dmitry Reznik

Reputation: 6862

There are couple of ways to do this. Also, I believe there has already been a discussion here on so. I'd recommend checking out this and this links.

Basically, here are the options as I see them:

  1. Convert your query into a stored procedure and use it to parse incoming parameter string into real values. And then you can run your query. There are couple of implementations on so and on the net.
  2. Use Table-valued parameter - a special parameter that is used to define multiple variables in a form of a table.
  3. Use temporary table - fill it with necessary tags and then make a join with it. Messy way to go.
  4. Transform one query into n queries
  5. Fill query string instead of parameterization.

I'd recommend going with the second one, though it may not be portable to other databases (e.g. MySql doesn't have a notion of table-valued parameters). And definitely look at the articles - they are a great read.

Upvotes: 1

Related Questions