OUT Parameter Issue in DB2 Stored Procedure

Question

I am getting an error complaining about the OUT Parameter ID as an Undefined name when I try to set its value at the end of the procedure. Commenting it out the procedure executes fine. What am I doing wrong?

CREATE PROCEDURE P3.CUST_CRT(IN NAME VARCHAR(15), 
                             IN GENDER CHAR(1), 
                             IN AGE INTEGER, 
                             IN PIN INTEGER,
                             OUT ID INTEGER)
LANGUAGE SQL
P1: BEGIN
        --Check if Customer NAME is NULL.
        IF NAME IS NULL THEN
                SIGNAL SQLSTATE VALUE '20010'
                SET MESSAGE_TEXT = 'No NAME.';
        END IF;
        --Check if Customer NAME is an empty string.
        IF NAME = '' THEN
                SIGNAL SQLSTATE VALUE '20020'
                SET MESSAGE_TEXT = 'NAME cannot be an empty string.';
        END IF;
        --Check if Customer GENDER falls in either of the two acceptable categories.
        IF GENDER NOT IN ('M','F') THEN
                SIGNAL SQLSTATE VALUE '20030'
                SET MESSAGE_TEXT = 'GENDER can either be M or F';
        END IF;
        --Check if Customer AGE is not null.
        IF AGE IS NULL THEN
                SIGNAL SQLSTATE VALUE '20040'
                SET MESSAGE_TEXT = 'AGE cannot be NULL';
        END IF;
        --Check that AGE is not negative.
        IF AGE < 0 THEN
                SIGNAL SQLSTATE VALUE '20060'
                SET MESSAGE_TEXT = 'AGE cannot be negative.';
        END IF;
        --Check that the Customer is an adult.
        IF AGE < 18 THEN
                SIGNAL SQLSTATE VALUE '20070'
                SET MESSAGE_TEXT = 'You have to be over 18 years to have an account.';
        END IF;
        --Check that PIN is not null.
        IF PIN IS NULL THEN
                SIGNAL SQLSTATE VALUE '20080'
                SET MESSAGE_TEXT = 'PIN cannot be empty.';
        END IF;
        --Pin cannot be less than zero.
        IF PIN < 0 THEN
                SIGNAL SQLSTATE VALUE '20090'
                SET MESSAGE_TEXT = 'PIN cannot be less than 0.';
        END IF;

        INSERT INTO P3.CUSTOMER(Name, Gender, Age, Pin) VALUES(NAME, GENDER, AGE, P3.ENCRYPT(PIN));
        SET ID = ID.CURRVAL;
END P1 @

Answer 1

Always specify your Db2 Server version and operating-system when asking for help. Never write "getting an error" unless you specify the exact error message and error code. You code presumes that the sequence object you named "ID" is in the schema, and you should not allow an output parameter name to be the same as a sequence name. Give a sequence object a different name from the output parameter

For "Code: -204, SQL State: 42704] "DB2ADMIN.ID" is an undefined name.. SQLCODE=-204, SQLSTATE=42704, DRIVER=4.22.29", Db2 is telling you that the output parameter (named ID) has the same name as the sequence object, and that Db2 cannot find the sequence object in the schema called DB2ADMIN - that is the schema that you are connecting to the database to do the compile.

So, either qualify your sequence object name (i.e. put the schema name before it for example P3.ID (if that is your sequence fully qualified name), or give the correct full name for your sequence object. The sequence object needs to exist in the specified or implied schema before your code will compile.

It's unclear from your code where you consume the next value of the sequence (you only use its current value), but that is a separate matter from the -204 - in other words you may have other errors.

If you seek to return the most recently consumed sequence value, then one way (there are other ways, including more elegant ways) to do it is like this (in this example the sequence object is pre-created as P3.THEID ):

INSERT INTO P3.CUSTOMER(ID, Name, Gender, Age, Pin) VALUES( NEXT VALUE FOR P3.THEID , NAME, GENDER, AGE, P3.ENCRYPT(PIN));

SET ID = P3.THEID.CURRVAL;

Answer 2

Your ID variable is defined as INTEGER while in order to use the CURRVAL function it has to be defined as a SEQUENCE object.