How to define "IN"-Values as an sqlparameter?

[TF]

Hi

Lets say I have a query like this one:
SELECT column1 FROM table WHERE column2 IN (2,5,8)

How do I substitute the 2,5,8 values with @ parameters and sqlparameter
objects? Is it possible?
I am currently just changing the commandtext of the sqlcommand by inserting
the string og comma separated values, but this hurts performance AFAIK.

Regards,
TF

 

[Miha Markic [MVP C#]]

Hi,

Hi

Lets say I have a query like this one:
SELECT column1 FROM table WHERE column2 IN (2,5,8)

How do I substitute the 2,5,8 values with @ parameters and sqlparameter
objects? Is it possible?

Unfortuantelly not.

I am currently just changing the commandtext of the sqlcommand by
inserting the string og comma separated values, but this hurts performance
AFAIK.

It doesn't hurt performances too much. The bigger problem is a possibility
of a Sql injection attack.
There are some workarounds, like creating a table that hosts IN values, fill
it (eventually you can use a stored procedure for this), and do a SELECT
with INNER JOIN on that table.

 

[Anton Sokolovsky]

If you can't live without it you can do it using stored procedure.

Client Code:

SQLVarcharListCreator _Params = new SQLVarcharListCreator();
SQLNvarcharListCreator _Values = new SQLNvarcharListCreator();
_Params.AddValue("Myparam"); _Values.AddValue('2');
_Params.AddValue("Myparam"); _Values.AddValue('5');
_Params.AddValue("Myparam"); _Values.AddValue('8');
.....
Command.CommandType = CommandType.StoredProcedure;
Command.Parameters.Add("@Params", _Params.GetList());
Command.Parameters.Add("@Values", _Values.GetList());
.....

and appropriate handling in stored procedure

Ream more here
http://www.codeproject.com/cs/database/PassingArraysIntoSPs.asp