Re: Building a SQL statement...

I assume your stored procedure accepts a single argument for the IN clause
(i.e. Parm1), right?
That is why it only works when you input a single value in it.

The IN clause can handle a variable number of arguments. But your procedure
only accepts one, so you can only put one in the IN clause.

Now, let's say you don't want to overload your stored procedure to accept
multiple arguments, but instead only ever want to register a single
procedure and have it treat the Parm1 argument as a comma separated list of
values to be used in the IN clause. You CAN do that.
You'll need to use dynamic SQL in your stored procedure (i.e. prepare/open).
You will build the entire SQL statement string based on the input parms you
receive as arguments. Your Parm1 argument should come in as IN
('token1','token2','token3',...).
Then you prepare the statement from the statement text you've built
dynamically. And finally open the prepared statement. This last step
(open) will make the result set available to the client (VB or Run SQL
Scripts, it doesn't matter).

Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: [SystemiDotNet] Building a SQL statement...

Hello,

I'm in the middle of writing a VB app that interacts with the iSeries.
The windows form captures some data entered by the user, builds some
parameters, and executes a stored procedure to return a data set. The
problem I'm having is that one of the parameters is a list of values to
be used in an IN clause.

For example:

Select * from myFile where field1 in (parm1)

I build parm1 in VB as a list of values like this: 'VALUE1', 'VALUE2',
'VALUE3'. I include the single quotes with the parm. If I pass just
one value, I do get a data set back, but when I pass several values in
the IN expression, I don't get anything back. Has anybody done
something like this? If so, how did you make it work?

So far I have not had luck in getting a data set back. I fact, I've
been trying to call the stored procedure from iSeries navigator typing
the list of values but I have not been able to make it work. Can
anybody offer any suggestions or alternatives?

Thank you,

Antonio Mira