I have a dynamic stored procedure, which I'm using to run multiple select queries. I have defined something like below.
CREATE PROCEDURE DYNAMIC
(IN IN_COLUMN1 VARCHAR(150),
IN IN_COLUMN2 VARCHAR(500),
IN IN_COLUMN3 VARCHAR(500),
IN IN_COLUMN4 CHAR(08),
IN IN_COLUMN5 DATE,
IN IN_COLUMN6 CHAR(05),
OUT OUT_COLUMN1 CHAR(01),
OUT OUT_COLUMN2 DEC(4,0),
OUT OUT_COLUMN3 DEC(4,0),
OUT OUT_COLUMN4 CHAR(04),
OUT OUT_COLUMN5 DATE
The problem here, when I run Query1, I will have input passed from COBOL DB2 program in IN_COLUMN1,IN_COLUMN2,IN_COLUMN3 and OUTPUT will be fetched into OUT_COLUMN1. I will initialize all INPUT in program, Due to the other OUTPUT parameters like OUT_COLUMN2,OUT_COLUMN3,OUT_COLUMN4 and OUT_COLUMN5 will have null, I'm getting SQLCODE "-305".
To fix this I tried to set OUTPUT parameters to default like below and got error while deploying.
OUT OUT_COLUMN2 DEC(4,0) DEFAULT NULL,
Is there any way to handle this. I'm using COBOL to call the stored procedure running in DB2.
Assuming IBM Enterprise COBOL,
To handle null values in COBOL host variables in SQL you need to assign a "null indicator" for each nullable variable.
See: Using host variables in SQL Statements (with examples in COBOL).
The null indicator will typically be negative if the result variable is null.
By default, all parameters to a DB2 for z/OS Native SQL Stored Procedure are nullable. (Db2 for z/OS 12.0.0 - Creating Native SQL Procedures)
For other ways of solving what I perceive is the task at hand,
Whilst I think at least DB2 User-defined Functions may support function overloading, stored procedures does not. That could have been an alternative.
Otherwise might I suggest returning a dynamic result set from your stored procedure? Then it would be up to the caller of the stored procedure to handle different configurations of the result sets, which is totally doable in COBOL.
Regarding UDF Overloading:
"A schema can contain several functions that have the same name but each of which have a different number of parameters or parameters with different data types. Also, a function with the same name, number of parameters, and types of parameters can exist in multiple schemas."
Hope it was to some help.