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

Hi,

"TF" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

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/databa...aysIntoSPs.asp



"TF" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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
>
>