Subject Stored Procedure problem
Author sgharp
Hi All,

I recently had a problem where a TIB_Cursor or TIB_Query wouldn't
return the correct result set so I decided to put the process into a
stored procedure. Now the stored procedure works fine in IBExpert
but doesn't work when executed by a TIB_DSql control. I have to
assume that I'm not implementing something correctly. The
TIB_DSql.SQL statement is

execute procedure spUpdateAveIdealUsages;

The stored procedure is

create procedure spUpdateAveIdealUsages
declare variable iInvItemID BigInt;
declare variable dQtySold Double Precision;
declare variable iCycleDays Integer;
execute procedure spCalcItemUsages;

select CurrentCycleDays
from xGeneral
into :iCycleDays;

for select InvItemID, QtySold
from xItemUsages
into :iInvItemID, :dQtySold
execute procedure spSafeDivide(dQtySold, iCycleDays)
returning_values :dQtySold;

update xInvItem
set Cycle3Ideal = Cycle2Ideal,
Cycle2Ideal = Cycle1Ideal,
Cycle1Ideal = AveIdealUsage,
AveIdealUsage = :dQtySold
where (InvItemID = :iInvItemID);


This works correctly in IBExpert but sets all fields to zero in my
application. I assume the issue might be with transactions but I've
tried it with explicit transactions using a TIB_Transaction
component and without the component allowing IBO to use the default
transaction. What should I look for?