> Thanks for your assistance Svein,
>
> I am using TIBO 4.6.B
>
> I am test executing the procedure in the IBExpert stored procedure
> editor, not in the debugger.
>
> Here is my stored procedure which I have simplified. The first select
> is supposed to find the value for Proj_ID. The DOC row definitely
> contains the correct Proj_ID. But when I try to use it in the
> subsequent Insert the error occurs. If I substitute a fixed number
> instead of :PROJ_ID in the Insert, it works OK.

Sounds like Svein is right -> if the SELECT doesn't return anything,
PROJ_ID will not be filled and thus the INSERT INTO fails.

> So to recap:
>
> "The first time" means after restarting the program and opening the form:
>
> 1. Press button on form the first time executes SP with error.
> 2. Press button on form a second time executes SP without error.
> 3. Executing SP manually with IB_SQL tool or IBExpert, never an error.
> 4. Press button on form first time executes SP (having hard coded
> value in SP for Proj_ID) without error.
>
> I have a couple of insert triggers on the ITEM table, but none require
> Proj_ID or obviously would cause a problem.
>
>
> CREATE PROCEDURE ADD_A_FREIGHT_CHARGE_TEST (V_DOC_ID INTEGER)
> RETURNS (NEW_ITEM_ID INTEGER)
> AS
> DECLARE VARIABLE PROJ_ID INTEGER;
> DECLARE VARIABLE PARTY_ID INTEGER;
> BEGIN
>
> -- gets values for Proj_ID & Party_ID
> SELECT DOC.PROJ_ID, DOC.PARTY_ID FROM DOC WHERE DOC.DOC_ID = :V_DOC_ID
> /* substituting # 1186 for :V_DOC_ID does not solve the problem*/
> INTO :PROJ_ID, :PARTY_ID;
>
> INSERT INTO ITEM (PROJ_ID, ITEM_ORDER, ITEM_DESCR_CLIENT, PARTY_ID,
> ITEM_SPECORACCESSORY)
> VALUES
> (
> :PROJ_ID, -- ***ERROR IS HERE, works ok when using numeric value
> 0,
> 'Freight charge on supplier invoice - Doc ID #' || :V_DOC_ID,
> --:ITEM_DESCR_CLIENT,
> :PARTY_ID,
> 'F'
> )

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com