Check input variable for null in procedure

[Former Member]

Hello all, In my procedure i am checking the input parameter for null

begin 
if :ip_join_set_id = '' then 


  if :ip

i also tried if:ip_join_set_id is null

But it doesn't seem to go into the if condition.

How can i simulate an empty value or null input parameter?

Any ideas?

[anindya_bose · Accepted solution]

Hi Tommy

Below syntax seems to be working perfectly fine for me.

I just declared one local variable and assigned my input parameter ( IP_ZC) to local variable lv.

Then I checked if lv is blank , if yes, then I assigned a default value to it.

So, if this procedure is called with blank IP , it would use default value.

Cheers

Anindya

[Former Member · Accepted solution]

Thank you for the response. i tired the same thing but it didn't work. How did you specify your input parameter when trying to do a data preview?

[anindya_bose · Accepted solution]

Not sure if I got your question correctly. Can you please explain little bit what you are trying to achieve?

Please note NULL and Blank values are not same although they look similar.  You have to see your underlying data and see what is the default value for that column.  You can use the SQLs below.

SELECT *  

FROM "SYS"."TABLE_COLUMNS" 

WHERE "SCHEMA_NAME" = <'YOUR_SCHEMA'>   AND IS_NULLABLE = 'TRUE'.

SELECT *  

FROM "SYS"."TABLE_COLUMNS" 

WHERE "SCHEMA_NAME" = <'YOUR_SCHEMA'>   AND IS_NULLABLE = 'FALSE' .

Notice the change in DEFAULT_VALUE Column for the above statements .

You can also add TABLE_NAME in your Where clause to further restrict the data set.

Cheers

Anindya

[Former Member · Accepted solution]

Hello,

I meant when i am trying to do a data preview, i put the operator Is Null or I put the value as '' for ip_join_set_id, however, the condition still does not evaluate to true.

How can I set ip_join_set_id = null when doing a data preview to test my procedure?

[anindya_bose · Accepted solution]

that depends on the data type 

Did you try with 0 or ' '  , i.e a space between  single quotes?

What is the data type of the field you are trying to filter with input parameter? What is the data type of the input parameter ? 

If my understanding is correct, you are creating a procedure with Input Parameter .

Cheers

Anindya

[Former Member]

Hi Tommy,

One workaround which I used to do is to check if length is zero.

Eg :

     if length(:ip_join_set_id) = 0 then  

          /* Do the operation which needs to be done upon null condition being met.

Regards,

Anil

[rindia]

Null can be handle like this:

DROP PROCEDURE CHECK_NULL;

CREATE PROCEDURE CHECK_NULL (IN NAME VARCHAR (1), OUT USERNAME VARCHAR (20) ) AS


BEGIN
  IF :NAME IS NULL THEN
       USERNAME := 'N/A';
  ELSE
       USERNAME := 'USER IS NOT NULL';
  END IF;
END;

CALL CHECK_NULL(?,?) returns "N/A" (do not input anything and execute)

CALL CHECK_NULL('RAJ',?) returns "USER IS NOT NULL"