> >Everyone,
> >
> >If I test a SP via the SQL editor on IBExpert things sorta work.. if I
> >execute this:
> >
> > select * from FIND_ITEM_CAT (9, 72, 1, 2);
> >
> >The editor outputs the following.. literally.. not in any table
> >format.. (the values returned are correct.):
> >
> > Procedure executing results:
> >
> > ISNEW = 0
> > I_C_ID = 433
> >
> >In any other SQL editor the above returns nulls.
>
> Yup, correctly.
>
> I don't use IBExpert, so I don't know exactly what its debugger is

showing

> you - probably some kind of simulation.
>
> But any interactive query interface will return an empty dataset if

you try

> to select from your SP, since is not written as a selectable procedure.
>
> If you use IB_SQL (or some other tool that is capable of showing the
> structure returned from an executable SP) to test your procedure,

and you

> use EXECUTE PROCEDURE, you will be able to read the return values

from your

> executable SP. IBExpert's interactive query tool possibly has this
> ability, too.

You are right (of course).. with mine or your version of the SP only
IB_SQL (and IBExpert) were able to show the returned values.
IBConsole, for instance, gives the null return values with any version
of the SP. This is even in IBConsole's SP editor.

I guess my problem is trying to programmatically get the return
value(s). I am new to the world of doing SP programming so I was
expecting the values to be returned in a pseudo table format.

> >If I do this command in ANY SQL editor (including IBExpert):
> >
> > select * from FIND_ITEM_CAT (9, 72, 1, 2);
> >
> >I get a table back with two columns filled with nulls.
> >
> >What the heck am I doing wrong?
>
> 1. You're assuming that a selectable SP and an executable SP are

the same

> thing. (They are not).

Yeah.. I goofed up.. I ment to put the 'execute procedure' invocation
version above.

> 2. Of more serious concern is the "design plan" (albeit failed) to

try to

> make your procedure both executable and selectable. It can be done,

but

> it's not recommended. The problem is that you are potentially going to
> insert a row into a table *and* return uncommitted data to the client
> interface, presumably to some structure that would fool the user into
> thinking s/he is looking at committed data.

Returning the GENERATOR value should be pretty safe even if the
transaction fails, right?

> >Here is my SP:
> >
> >SET TERM ^ ;
> >
> >CREATE PROCEDURE FIND_ITEM_CAT (
> > D_ID INTEGER,
> > C_ID INTEGER,
> > S_ID INTEGER,
> > G_ID INTEGER)
> >RETURNS (
> > ISNEW CHAR(1),
> > I_C_ID INTEGER)
> >AS
> /*
> >DECLARE VARIABLE RECCOUNT INTEGER = 0;
> >DECLARE VARIABLE TMPID INTEGER = 0;
> >DECLARE VARIABLE RECNO INTEGER = 0;
>
> **** No. These variables are all superfluous ***** */
>
> >begin
>
> /* Although the following block of code isn't related to your "apparent
> problem", it's a wrong approach to use record counts for existential

tests.

> >SELECT count(ID) from ITEM_CATEGORY
> > WHERE DEPARTMENT_ID = :D_ID AND CLASS_ID = :C_ID AND STYLE_ID =
> >:S_ID AND GENDER_ID = :G_ID INTO :RECCOUNT;
> > IF (RECCOUNT = 0) THEN
>
> Replacement code follows....though there are other ways to do

existential

> tests.
> */
>
> ic_c_id = -999;
>
> SELECT ID FROM ITEM_CATEGORY
> WHERE DEPARTMENT_ID = :D_ID
> AND CLASS_ID = :C_ID
> AND STYLE_ID = :S_ID
> AND GENDER_ID = :G_ID
> INTO :I_C_ID;
> if (i_c_id is null or i_c_id = -999) then
> BEGIN
> > i_c_id = GEN_ID(GEN_ITEM_CATEGORY_ID,1);
> > ISNEW = '1';
> > INSERT INTO ITEM_CATEGORY
> > (ID, DEPARTMENT_ID, CLASS_ID, STYLE_ID, GENDER_ID)
> > VALUES (:i_c_id, :D_ID, :C_ID, :S_ID, :G_ID);
> > END
>
> else
> ISNEW = '0';
>
> /* Now, to make this a selectable SP (not recommended) you would

need to add

> the following statement here: */
>
> SUSPEND;
>
> >end
> >^
> >
> >SET TERM ; ^
>
> ./hb