Re: Stored procs: detecting when SELECT returns no records

Billy Wheeler wrote: > > >Basically, here's the *pseudo* code for what I'm trying to do in > >this particular procedure. If someone would be kind enough to > >suggest the proper way to accomplish this in an Informix stored > >procedure, I'll be very grateful: > > > >SELECT quantity FROM history > > WHERE history.sku = sku AND history.date = date;> > > >IF no record found > > INSERT new record into history table > >ELSE > > UPDATE quantity in existing record in history table > > Dear Andrew, > > As an old and wizend (*not* wise!) programmer, I would INSERT and if the > INSERT failed on a -239 error (Duplicate value in unique key), I would UPDATE. > > HTH. > -- > It depends upon whether you are more likely to do an INSERT or UPDATE. If I have a table which is typically updated, I perform the UPDATE and then check the number of rows processed. If that comes back 0, then I perform an INSERT. With mostly new data, however, Billy's solution is clearly the best. John Prideaux alastl@ix.netcom.com