I need to formulate a query that tests that a field is a member of a
list of acceptable values, but can't figure out the mechanics. Say we
have a table with a column containing an ID. I have a list of
interesting ids in my application, for which I'd like to write a query:

SELECT * FROM table where ID in (list of values)

where (list of values) is the array of interesting ids that my
application has already built. The simple solution of selecting the
interesting IDs within the query is not available as it takes some
significant business logic to build it. Is there some form of Array
parameter that I can use to bind the values I'm interested in into a
parameter in my SQL statement? I'd love something like:

SqlCommand cmd = connection.CreateCommand();
cmd.CommandText = "SELECT * FROM table WHERE ID in
(@ArrayParameter)";
cmd.Parameters.Add("@ArrayParameter", ????);
cmd.Parameters["@ArrayParamter"].Values = <ArrayList Variable>;

No; you can't put a list of values as a parameter.

You can write a wrapper of SqlCommand, to extend SqlCommand to work with
this types of parameter values (arrays); and internally to put your unsual
parameter value right in the sql string command.CommandText, before send it
to the Sql Server. And this will work transparently like you want. I did
this and is working beautiful.

Dumitru Sbenghe


<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I need to formulate a query that tests that a field is a member of a
> list of acceptable values, but can't figure out the mechanics. Say we
> have a table with a column containing an ID. I have a list of
> interesting ids in my application, for which I'd like to write a query:
>
> SELECT * FROM table where ID in (list of values)
>
> where (list of values) is the array of interesting ids that my
> application has already built. The simple solution of selecting the
> interesting IDs within the query is not available as it takes some
> significant business logic to build it. Is there some form of Array
> parameter that I can use to bind the values I'm interested in into a
> parameter in my SQL statement? I'd love something like:
>
> SqlCommand cmd = connection.CreateCommand();
> cmd.CommandText = "SELECT * FROM table WHERE ID in
> (@ArrayParameter)";
> cmd.Parameters.Add("@ArrayParameter", ????);
> cmd.Parameters["@ArrayParamter"].Values = <ArrayList Variable>;
>

Guys,

Just be aware of sql injection attacks.

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

"Dumitru Sbenghe" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> No; you can't put a list of values as a parameter.
>
> You can write a wrapper of SqlCommand, to extend SqlCommand to work with
> this types of parameter values (arrays); and internally to put your unsual
> parameter value right in the sql string command.CommandText, before send
> it
> to the Sql Server. And this will work transparently like you want. I did
> this and is working beautiful.
>
> Dumitru Sbenghe
>
>
> <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> I need to formulate a query that tests that a field is a member of a
>> list of acceptable values, but can't figure out the mechanics. Say we
>> have a table with a column containing an ID. I have a list of
>> interesting ids in my application, for which I'd like to write a query:
>>
>> SELECT * FROM table where ID in (list of values)
>>
>> where (list of values) is the array of interesting ids that my
>> application has already built. The simple solution of selecting the
>> interesting IDs within the query is not available as it takes some
>> significant business logic to build it. Is there some form of Array
>> parameter that I can use to bind the values I'm interested in into a
>> parameter in my SQL statement? I'd love something like:
>>
>> SqlCommand cmd = connection.CreateCommand();
>> cmd.CommandText = "SELECT * FROM table WHERE ID in
>> (@ArrayParameter)";
>> cmd.Parameters.Add("@ArrayParameter", ????);
>> cmd.Parameters["@ArrayParamter"].Values = <ArrayList Variable>;
>>
>
>

And be aware of limited stack size when compiling queries. Imagine 20000 IDs
passed ....

There is a technique to pass parameters as binary arrary to stored
procedures and to unpack it as sql server side, but I can't recall the
location of documentation for this.


"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
news:(E-Mail Removed)...
> Guys,
>
> Just be aware of sql injection attacks.
>
> --
> Miha Markic [MVP C#] - RightHand .NET consulting & development
> SLODUG - Slovene Developer Users Group
> www.rthand.com
>
> "Dumitru Sbenghe" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > No; you can't put a list of values as a parameter.
> >
> > You can write a wrapper of SqlCommand, to extend SqlCommand to work with
> > this types of parameter values (arrays); and internally to put your
unsual
> > parameter value right in the sql string command.CommandText, before send
> > it
> > to the Sql Server. And this will work transparently like you want. I did
> > this and is working beautiful.
> >
> > Dumitru Sbenghe
> >
> >
> > <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >> I need to formulate a query that tests that a field is a member of a
> >> list of acceptable values, but can't figure out the mechanics. Say we
> >> have a table with a column containing an ID. I have a list of
> >> interesting ids in my application, for which I'd like to write a query:
> >>
> >> SELECT * FROM table where ID in (list of values)
> >>
> >> where (list of values) is the array of interesting ids that my
> >> application has already built. The simple solution of selecting the
> >> interesting IDs within the query is not available as it takes some
> >> significant business logic to build it. Is there some form of Array
> >> parameter that I can use to bind the values I'm interested in into a
> >> parameter in my SQL statement? I'd love something like:
> >>
> >> SqlCommand cmd = connection.CreateCommand();
> >> cmd.CommandText = "SELECT * FROM table WHERE ID in
> >> (@ArrayParameter)";
> >> cmd.Parameters.Add("@ArrayParameter", ????);
> >> cmd.Parameters["@ArrayParamter"].Values = <ArrayList Variable>;
> >>
> >
> >
>
>

I'am always aware of that .

This is one of the reason I prefer sql statements with parameters; which is
not possible for "IN" case. And my solution work without problems if you
code it properly ...


"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
news:(E-Mail Removed)...
> Guys,
>
> Just be aware of sql injection attacks.
>
> --
> Miha Markic [MVP C#] - RightHand .NET consulting & development
> SLODUG - Slovene Developer Users Group
> www.rthand.com
>
> "Dumitru Sbenghe" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > No; you can't put a list of values as a parameter.
> >
> > You can write a wrapper of SqlCommand, to extend SqlCommand to work with
> > this types of parameter values (arrays); and internally to put your
unsual
> > parameter value right in the sql string command.CommandText, before send
> > it
> > to the Sql Server. And this will work transparently like you want. I did
> > this and is working beautiful.
> >
> > Dumitru Sbenghe
> >
> >
> > <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >> I need to formulate a query that tests that a field is a member of a
> >> list of acceptable values, but can't figure out the mechanics. Say we
> >> have a table with a column containing an ID. I have a list of
> >> interesting ids in my application, for which I'd like to write a query:
> >>
> >> SELECT * FROM table where ID in (list of values)
> >>
> >> where (list of values) is the array of interesting ids that my
> >> application has already built. The simple solution of selecting the
> >> interesting IDs within the query is not available as it takes some
> >> significant business logic to build it. Is there some form of Array
> >> parameter that I can use to bind the values I'm interested in into a
> >> parameter in my SQL statement? I'd love something like:
> >>
> >> SqlCommand cmd = connection.CreateCommand();
> >> cmd.CommandText = "SELECT * FROM table WHERE ID in
> >> (@ArrayParameter)";
> >> cmd.Parameters.Add("@ArrayParameter", ????);
> >> cmd.Parameters["@ArrayParamter"].Values = <ArrayList Variable>;
> >>
> >
> >
>
>

"Dumitru Sbenghe" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I'am always aware of that .
>
> This is one of the reason I prefer sql statements with parameters; which
> is
> not possible for "IN" case.

Every SQLServer person I know I ask: hey guys, why don't you give us
parameters of array type.
And guess what, nobody knows. Go figure.

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


And my solution work without problems if you
> code it properly ...
>

Hello, I need immediate help with this problem.

>From C# code I am trying to call a stored procedure, which is in a
oracle 8i db.





First of all here is the definition of the function(oracle function).

//==================================================================

FUNCTION INSERT_COMMENTS( iv_acc_id IN VARCHAR2,

iv_user_id IN VARCHAR2,

iv_comment IN VARCHAR2 )

RETURN BOOLEAN;

//==================================================================









My Catch catches this error code coming for Oracle side.

//==================================================================

:In insertComments2 method => ORA-06550: line 1, column 7:

PLS-00306: wrong number or types of arguments in call to
'INSERT_COMMENTS'

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

//==================================================================











And here is my C# code that tries to make the call to the oracle.

//==================================================================

public static void insertComments2(string accID)

{

try

{

string useridv = "DAYBREAK";

string STR_COMMMENT = "Wellcome letter has been
generated";


//==============================================================================

//create an instance of the command object
giving the procedure name

OleDbCommand sqlCmd2 = new
OleDbCommand("ACCP50.INSERT_COMMENTS",myConn) ;



// Define the command type u r executing as a
Stored Procedure.

sqlCmd2.CommandType =
CommandType.StoredProcedure ;




sqlCmd2.Parameters.Add("iv_acc_id",OleDbType.VarChar,20);

sqlCmd2.Parameters["iv_acc_id"].Direction =
ParameterDirection.Input ;




sqlCmd2.Parameters.Add("iv_user_id",OleDbType.VarChar, 20);

sqlCmd2.Parameters["iv_user_id"].Direction =
ParameterDirection.Input ;




sqlCmd2.Parameters.Add("iv_comment",OleDbType.VarChar,40);

sqlCmd2.Parameters["iv_comment"].Direction =
ParameterDirection.Input ;



sqlCmd2.Parameters.Add("RETURN
BOOLEAN",OleDbType.Boolean);

sqlCmd2.Parameters["RETURN BOOLEAN"].Direction
= ParameterDirection.ReturnValue;



detailLog(accID,w);



//sqlCmd2.Parameters["RETURN
BOOLEAN"].Direction = ParameterDirection.ReturnValue;

//sqlCmd2.Parameters["RETURN BOOLEAN"];



sqlCmd2.Parameters["iv_acc_id"].Value = accID;
;



sqlCmd2.Parameters["iv_user_id"].Value =
useridv;



sqlCmd2.Parameters["iv_comment"].Value =
STR_COMMMENT;



// execute the stored procedure

sqlCmd2.ExecuteNonQuery();



// if ((string) (sqlCmd2.Parameters["RETURN
BOOLEAN"].Value.ToString()) == "true")

// detailLog(" Success. Comments has been
inserted successfully.", w);

// else

// detailLog(" FAILED. Comment insertion
failed.", w);



}

catch (Exception error)

{

detailLog("In insertComments2 method => "
+error.Message, w);

System.Console.Write(error.Message);

}

}



//==================================================================

here you are
http://www.codeproject.com/cs/databa...aysIntoSPs.asp


"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
news:%(E-Mail Removed)...
>
> "Dumitru Sbenghe" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > I'am always aware of that .
> >
> > This is one of the reason I prefer sql statements with parameters; which
> > is
> > not possible for "IN" case.
>
> Every SQLServer person I know I ask: hey guys, why don't you give us
> parameters of array type.
> And guess what, nobody knows. Go figure.
>
> --
> Miha Markic [MVP C#] - RightHand .NET consulting & development
> SLODUG - Slovene Developer Users Group
> www.rthand.com
>
>
> And my solution work without problems if you
> > code it properly ...
> >
>
>