Subject Re: Stored procedure array input parameters
Author Svein Erling Tysvær
Of course it is possible to use only SPs as the interface, but they are not
suitable for a variable number of variables ("records") as input or output. You
do loose quite a bit of flexibility when trying to force something naturally
two-dimensional into one dimension (aggregates are a bit different), and I guess
you will have to choose between a few not-too-ideal options (calling the SP a
variable number of times, check for nulls or even pass large character strings
with fixed length and decode in the SP).


> I considered that - I may still go there. My idea, reasonable or not,
> is to basically perform all my data retrieval and storage via stored
> procedures. I'm still playing with it - but it makes a lot of things
> much easier from an application coding point of view. I suppose filling
> a "work" table and then calling an "update" procedure isn't too terrible
> - but I'd still prefer the elegance of a single procedure call. Then
> the only SQL syntax used in my app would be the procedure calls.
> >In general, tables are quite good at storing a variable number of records
> >*G*. What about storing the records (or the PK of the records) in a table
> >together with a number obtained from a generator (same number for all records
> >in the set), and then just pass this number to the SP?
> >
> >>Are there any plans to implement array input parameters for stored
> >>procedures?
> >>
> >>I'm trying to use stored procedures to hide the database implementation
> >>from my application - so I can change my table structure with virtually
> >>no changes to my app. So far, it's gone reasonably well.
> >>
> >>My problem now is trying to work with a group of records as a single
> >>entity. I didn't want to use array columns as this requires a fixed
> >>dimension - and I wanted it variable. But now I don't see how to send a
> >>variable amount of information to a procedure for storage. The only way
> >>I see it now is to declare a fixed list of input parameters - more than
> >>I anticipate storing, and then checking those parameters for null
> >>values. This seems clumsy.
> >>
> >>If someone has a better idea, please share!
> >>
> >>Daniel