Johan and Martijn,

> > The problem is that when the sql statement (eg.)
> > SELECT Stock FROM RESM_STOCK WHERE ItemIdx = :ItemIdx
> INTO :StockQty;
> > is executed in the procedure and there is no result for the current
> ItemIdx,
> > it containts the result of the previous select where ther
> was a record for
> > Itemidx.
> > eg
> > Select ... ItemIdx = 5 -> result 10
> > Select ... itemidx = 1 -> (no record for it) return also 10
>
> I'm pretty sure it doesn't return "10" - what makes you think it does?

Johan is absolutely correct. If there's no record to fetch, then nothing is
assigned to the variable/parameter (StockQty), so it still contains an old
value. And it's not a bug, but as designed. The only way to handle it
properly is initializing variable/parameter before SELECT and checking its
value after it, i.e. exactly how Johan has done it.


Dmitry