Adding Parameter SQL IN

Question

I just say if i have a SQL like this;

SELECT B.HESAP_NO
FROM S_TEKLIF B
WHERE B.HESAP NO = 234

I can add some parameters this SQL programaticly like this C# code;

      if (txtBoxText1 != "")
      {
          strQuery = strQuery + " AND A.ISL_TAR >= @S_TARIH_B";

          dt_stb = DateTime.Parse(txtBoxText1);
          myCommand.Parameters.AddWithValue("@S_TARIH_B", dt_stb);
      }

With this code, i can add AND "A.ISL_TAR >= @S_TARIH_B" to i my SQL.

BUT, in SQL IN operators i don't know how can i add some parameters.

For example;

SELECT A.HESAP_NO
FROM S_TEKLIF A
WHERE A.HESAP_NO IN (SELECT A.HESAP FROM S_TEKLIF WHERE A.MUS_K_ISIM = 
for (int counter = 0; counter < list.Items.Count; counter++)
  {
 if (list.Items[counter].Selected)
{
 values.Add(list.Items[counter].Value);
 }

  })

How can i add a parameters to SQL IN ?

SWeko · Accepted Answer · 2011-03-22 12:48:20Z

2

There are a number of ways to achieve this, and all of them are described in the Arrays and Lists in SQL Server article. It's a gread read for any SQL developer.

answered Mar 22, 2011 at 12:48

SWeko

30.9k11 gold badges74 silver badges107 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Soner Gönül Over a year ago

Maybe i should to try this way. Firstle i assign all A.MUS_K_ISIM to "List" and then "cmd.CommandText = "SELECT ProductID, ProductName FROM Northwind..Products " & _ "WHERE ProductID IN (" & List & ")" " Right??

SWeko Over a year ago

I'm still more a fan of using a user function is SQL Server, than constructing a query in the code, but, yes, that could work, provided the List thing is an actual comma separated string.

MatBailie · Accepted Answer · 2011-03-22 12:47:54Z

0

I'm not aware of any way to pass a list/array as a single parameter for use as part of an IN clause. Your best option may be to build up the IN clause yourself as a string.

Alternatively, you could do this in steps.
1. Create a temp table with just an ID field
2. Create a parameterised query to insert one value into that temp table
3. Loop through your values, calling the sql to insert values into the temp table
4. Exexute your main SQL query, joining on to the temp table instead of using IN

You just need to ensure you don't close and re-open you session each time, thus ensuring your temp table is persisted and retains the values you've been inserting.

answered Mar 22, 2011 at 12:47

MatBailie

87.6k19 gold badges113 silver badges144 bronze badges

Collectives™ on Stack Overflow

Adding Parameter SQL IN

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related