Subject Re: [firebird-support] Re: Problem with my first stored procedure
Author Martijn Tonies
Hello Adam,

> Not bad for your first SP. I am not familiar with FIB so I can't tell
> you if you are using that right, but here is some observations from
> the firebird code.
> Your stored procedure is a selectable stored procedure (because it
> returns something).
> Basically
> select Result_ID
> from P_SAVE_DATA_LIST_ITEM (1,'test',1);

Please - NEVER do this on a procedure that INSERTs or
UPDATEs or executes other procedures.

ONLY use this for procedures that actually return/create a

> should return you something if you run it in iSQL or any other
> Firebird tool. (replace the parameters with something that makes
> sense in your database).
> Your first problem is that it will not return anything. Firebird uses
> the keyword suspend to output a record from a stored procedure. Every
> time you call suspend, whatever is sitting in the output variables of
> the stored procedure is returned.
> So place
> suspend;
> between the last two "end" statements in the stored procedure.

No, let's not do that. The procedure given is a procedure that
needs to be EXECUTEd.

EXECUTE PROCEDURE myproc (:input1, :input2, :input3)

> Then the above select statement should return as expected, and you
> can check the doco on the FIB components to make sure you are using
> them properly.

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Upscene Productions