Here is the query..

Just three tables.. Ive removed all indexes.. Its stripped with one record in each..

This returns a null for the first time.. perfect every time there after..Ive modified this stored proc a hundred diff ways.. makes no diff..



CREATE TABLE STORES_INVENTORY (
    REC_ID           INTEGER NOT NULL,
    STORES_REC_ID    INTEGER,
    INV_DATE         DATE,
    PRODUCTS_REC_ID  INTEGER
);


CREATE TABLE STORES (
    REC_ID    INTEGER NOT NULL,
    STOREID   VARCHAR(10) NOT NULL,
    DEPTID    VARCHAR(10) DEFAULT          '0',
    ISACTIVE  VARCHAR(1) DEFAULT 'Y',
    NAME      VARCHAR(35),
    ADDRESS1  VARCHAR(35),
    ADDRESS2  VARCHAR(35),
    CITY      VARCHAR(35),
    STATE     CHAR(2),
    ZIP       VARCHAR(9)
);



CREATE TABLE PRODUCTS (
    REC_ID    INTEGER NOT NULL,
    UPC_CODE  VARCHAR(15) NOT NULL,
    PRODUCT   VARCHAR(35)
);







CREATE PROCEDURE INVENTORY_PROC
returns (
    product varchar(40),
    si_rec_id integer)
as
begin
   For select P.product,SI.Rec_ID
   FROM stores S,Products P
   LEFT OUTER JOIN Stores_Inventory SI ON (SI.Products_Rec_ID=P.rec_id
        and S.Rec_ID=SI.stores_rec_id
        And SI.Inv_Date = '04/11/2014'
        )
   Where S.REC_ID=1036
   and P.upc_code = '17072-22000'
   Into  :product,  :si_rec_id
   do
   Begin

     suspend;
   End
end

 

>Look at the attached database.. there is only one stored procedure.. and very little data.. Only really one record..
>
>Start IB Expert.. run the stored Proc.. It will return Null.. (Not right)
>Run it again, and again after that, gives back the right value.. but
>always fails the first time..
>
>Any ideas? Am I doing something dumb?

The 'dumb' thing is that this list doesn't allow attachments, Gordon. So extract the text of the stored procedure (and possibly some table create and insert if required) and post it to this list (strip out parts that are irrelevant for your question, we don't want an SP of several thousand lines).

If your SP looks something like:

create MySP returns(a integer = ?) as
begin
a = (select coalesce(MyVariable, 0) from MySingularTable);
update or insert into MySingularTable(MyVariable)
values(gen_id(MyGenerator, 1));
suspend;
end

then this will of course return <null> if MySingularTable is empty (there's no row to select, so coalesce is not executed). Though I don't know whether your problem is similar to this or something completely different.

Set