At 11:14 PM 15/03/2005 +0000, you wrote:

>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.

>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).

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.

>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