DB2 stored proc with Out param always NULL

GCDevOps 0
Viewed 51

This is DB2 on the IBM I.

I use Run SQL Scripts to test my SP. enter image description here

I even tried to initialize the out parameter as follows and got the same result...

CALL SGDEDMGT.USPGETBLAHIDFROMBLAHNUM(
  BLAHNO => 'xx#########00000####',  
  /* IN  CHARACTER(20)    */
  BLAHID => '0'   /* OUT INTEGER          */
);

This is the result I get back...

 [ 09/09/2022, 03:45:30 PM ]  Run All...   
  CALL SGDEDMGT.USPGETBLAHIDFROMBLAHNUM( BLAHNO =>                       
  'xx#########00000####',  BLAHID => '0'  )   
  Return Code = 0 
  Output Parameter #2 (BLAHID) = <NULL>   
  Statement ran successfully   (141 ms)

I even tried the CALL with the simplest format, as follows and still receive the same result...

CALL SGDEDMGT.USPGETBLAHIDFROMBLAHNUM(
  'xx#########00000####',?);

I just don't understand why I'm getting null for BLAHID.

Here is my stored proc.

CREATE PROCEDURE USPGETBLAHIDFROMBLAHNUM (
    IN BLAHNO CHAR(20),
    OUT BLAHID INTEGER 
    )   
    LANGUAGE SQL
P1 : BEGIN
    DECLARE TMPP INTEGER;
    SET TMPP = (
    SELECT BLAHID
        FROM SGDEDMGT.BLAHS
        WHERE "BlahNumber" = BLAHNO ) ;
    SET BLAHID = TMPP;   
END P1

Btw, in case anyone is wondering, I sanity checked by doing a simple SELECT WHERE just to be sure the test data I'm filtering for is actually there as a returnable value :)

db2 stored-procedures sql
2 Answers

In Run SQL Scripts, you simply pass a parameter marker, ? , into the call statement for the output parm..

CALL SGDEDMGT.USPGETBLAHIDFROMBLAHNUM(
  BLAHNO => 'xx#########00000####',  
  /* IN  CHARACTER(20)    */
  BLAHID => ?   /* OUT INTEGER          */
);

--with positional parms
CALL SGDEDMGT.USPGETBLAHIDFROMBLAHNUM('xx#########00000####', ?);
userV4jc0

I found my issue. My fault for not really paying attention. In Line 9 of my Create Procedure example, "SELECT BLAHID" (Note, it's not the real column name and I apologize for being cryptic), the issue itself was BLAHID. It should've been BlahId and because the real column name is longer than 6 characters, it should be enclosed in double quotes like "BlahId". Why the RUN SQL SCRIPT tool nor post compilation revealed any error about the column name BLAHID not existing is a serious wonder which gave me the false sense of being accurate and thus, running around chasing my own tail. Thanks to everyone who spent some time in helping me try to figure out the problem. Much appreciated!

GCDevOps0
Related