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.


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?




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;
begin
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
BEGIN
TMPID = GEN_ID(GEN_ITEM_CATEGORY_ID,1);
ISNEW = '1';
INSERT INTO ITEM_CATEGORY (ID, DEPARTMENT_ID, CLASS_ID,
STYLE_ID, GENDER_ID)
VALUES (:TMPID, :D_ID, :C_ID, :S_ID, :G_ID);
I_C_ID = TMPID;
END
ELSE
BEGIN
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
:RECNO;
I_C_ID = RECNO;
ISNEW = '0';
END
end
^

SET TERM ; ^