Thanks! This makes some sense.

 

It's generally a bad idea to mix SQL-89 and SQL-92 (implicit vs explicit join), try to always do it explicitly (i.e. never use comma in your FROM clause).

I guess REC_ID = 1036 and upc_code = '17072-22000' are just for testing purposes. Moreover, I guess this stored procedure would only be interesting for products in P, whereas it might or might not matter whether the store actually has the product. Try changing your procedure to:

CREATE PROCEDURE INVENTORY_PROC
returns (
product varchar(40),
si_rec_id integer)
as
begin
FOR SELECT P.product, SI.Rec_ID
FROM Products P
LEFT JOIN Stores_Inventory SI ON SI.Products_Rec_ID=P.rec_id
AND SI.Inv_Date = '04/11/2014'
LEFT JOIN Stores S ON S.Rec_ID=SI.stores_rec_id
WHERE P.upc_code = '17072-22000'
AND S.REC_ID=1036
INTO :product, :si_rec_id
do
Begin
suspend;
End
end

Now, for this particular procedure where you still have S.REC_ID in your WHERE clause, it would be better to use [INNER] JOIN rather than LEFT [OUTER] JOIN, but I guess you might not always want S.REC_ID in your WHERE clause (this procedure could then give you data about the product and add information about store that have them, but it would include data for products that no stores have), hence I kept the LEFT JOIN.

HTH,
Set