Native SQL - Parameter Style General With Nulls

Hariprasad_Pargunan · New User Joined: 08 Jan 2009 Posts: 20 Location: Chennai

Hi,

I am writing a Native SQL Stored Procedure for both Insert and Update.

In my program, I have two input variables 1) New User Name and 2) Old User NAme. If I get the Values in both the fields then my SP would update the DB2 Table with New User Name. If there are no values in the Old User Name then an Insertion would happen.

I hope we can use PARAMETER STYLE GENERAL WITH NULLS in Native SQL. But I am not sure where we declare the Parameter Null indicators for the INput values. In COBOL we have the Parameter NULL indicators in Linkage Section using which, we can validate for nulls.

Could you please let me know how to check the NULL indicators in Native SQL.

EG:-
CREATE PROCEDURE EMP.NATIVE_SQL (
NEW_EMP_NAME IN CHAR(50)
, OLD_EMP_NAME IN CHAR(50)
)
LANGUAGE SQL
WLM ENVIRONMENT PRODWLM
MODIFIES SQL DATA
PARAMETER STYLE GENERAL WITH NULLS

Start: ...............

>> Checking for NEW_EMP_NAME for NULL would be suffice here. Please advise.

dick scherrer · Posted: Wed Mar 07, 2012 7:20 am

Hello,

I believe one of us is confused. . .

If you want to check some value in the program (field n a file or working-storage etc) for "NULL", i know of no way to do this - there is no NULL value in the ebcdic value table. Every value from x'00' to x'FF' is valie and is NOT a null value.

If you want to insert a null value, you would set the null-indicator to -1 which tells sql this value is numm (contains no value at all).

Possibly i misunderstand?

GuyC · Posted: Wed Mar 07, 2012 4:23 pm

how about :

IF NEW_EMP_NAME is NULL then SIGNAL ...;

IF OLD_EMP_NAME is NULL then
insert ...
ELSE
update ...
;