SqlParameter for IN-Clausel (int values)?

  • Thread starter Thread starter =?ISO-8859-1?Q?Roland_M=FCller?=
  • Start date Start date
?

=?ISO-8859-1?Q?Roland_M=FCller?=

Hi Ng,

i have a sucking problem......


select * from test where nId IN (1, 2, 3)


Now i want to execute this with an sqlcommand and more interesting with
an sqlParameter:

sqlCommand.CommandText = select * from test where nId in (@IN-Values)";

I first tried:

SqlParameter sqlParameter = new SqlParameter("@Values", "1, 2, 3");

Exeption: Syntax error converting the nvarchar value '1, 2, 3' to a
column of data type int

This is very logical. But how can i do?

I then tried to use an int32 array or an arraylist with int values:


int[] test = new Int32[] {1, 2, 3};
ArrayList test2 = new ArrayList();
test2.Add(1);
test2.Add(2);

SqlParameter sqlParameter = new SqlParameter("@Values", test);
SqlParameter sqlParameter = new SqlParameter("@Values", test2);

This didn't work, too. I cannot give an array or arraylist to a
parameter as value.


How can i resolve my problem? I don't want to do without sqlparameter
and i only want ONE sqlparameter! Is it impossible?


Thanks,
Roland
 
An easier way to do this is write a stored procedure that uses the fn_split
function. Go online and look for the fn_split function, put it in your
master table and use it. This will allow you to pass in an IN parameter or
any length and have it work. This is a glaring oversite on SQL Server part.
The link here is complicated.

John
Kerry Moorman said:
Roland,

http://support.microsoft.com/default.aspx?scid=kb;en-us;555167

Kerry Moorman


Roland Müller said:
Hi Ng,

i have a sucking problem......


select * from test where nId IN (1, 2, 3)


Now i want to execute this with an sqlcommand and more interesting with
an sqlParameter:

sqlCommand.CommandText = select * from test where nId in (@IN-Values)";

I first tried:

SqlParameter sqlParameter = new SqlParameter("@Values", "1, 2, 3");

Exeption: Syntax error converting the nvarchar value '1, 2, 3' to a
column of data type int

This is very logical. But how can i do?

I then tried to use an int32 array or an arraylist with int values:


int[] test = new Int32[] {1, 2, 3};
ArrayList test2 = new ArrayList();
test2.Add(1);
test2.Add(2);

SqlParameter sqlParameter = new SqlParameter("@Values", test);
SqlParameter sqlParameter = new SqlParameter("@Values", test2);

This didn't work, too. I cannot give an array or arraylist to a
parameter as value.


How can i resolve my problem? I don't want to do without sqlparameter
and i only want ONE sqlparameter! Is it impossible?


Thanks,
Roland
Click to expand...
Click to expand...
 
Not really. There is a class with constant strings containing
sqlcommands; the are used from different places in a program or even in
different programs.
I take the contants and must fill them with MY current values
(parameters); i cannot easily modify the command text.
 
Back
Top