Subject Re: Stored procs and array parameters...
Author Greg At ACD
Thx for your response :)

The strings are in a memory linked list in my C++ client
application. They are retrieved from another source completely
(filesystem file name, for example), and then used as an indexed
reference in the database.

My application currently (for example) walks thru this list and call
a stored proc for each entry which returns information relating to a
record in the database related to this string, or if there is no
information, creates the new record. The information is returned as
a one record result set from the stored procedure.

Since this list can contain thousands of strings, the SP call is
make 1000 times. Over the network... this can be slow.

My preference is to call the stored procedure once with the list of
strings, and have it do this work, and return all 1000 records as a
result set of the stored procedure. To me, this would seem to be
more efficient.

... But perhaps I'm wrong in this assessment...


--- In, Svein Erling Tysvær
<svein.erling.tysvaer@k...> wrote:
> Where is that array of strings located and what should the stored
> procedure do with it? In some cases it may be possible to do
> like
> SELECT sp.outputparemeter
> FROM tablewithstrings tws
> LEFT JOIN storedprocedure(tws.stringfield) sp on (1=1)
> but this of course doesn't work if you, say, want to do some
> aggregation like concatenating the fields in the stringlist.
> HTH,
> Set
> --- In, "Greg At ACD" wrote:
> > Hi all,
> >
> > I realize that in FB 1.5 there is no support for passing arrays
> > parameters to stored procedures (e.g. array of integers or
> > Is this something that is being considered for a future release?
> > alternatives to this that may work?
> >
> > One possiblity would be to pass in a comma separated string to
> > stored proc, and have the stored proc parse it, but I keep
> > that having the calling application generate this and have the
> > stored proc parse it would take more time than its worth :( .
> >
> > In one situation, my client application (VC++, SQLAPI interface)
> > calls a particular stored procedure once for each element in an
> > array of strings. Of course, each call has some overhead (create
> > SQL, prepare (parse) over network, execute, package results,
> > back over nework, repeat), so if I could call an SP that would
> > the array of strings as input, it would likely be more
> > efficient... :)
> >
> > thx!
> >
> > Greg