stored procedures not excepting NULL

[Former Member]

CREATE PROCEDURE SCHEMA.INS_CUSTOMER_EVENT_NULL96

(

     IN EVENT_TYPE NVARCHAR(29)

)    

      LANGUAGE SQLSCRIPT AS

      BEGIN

            INSERT INTO SCHEMA.CUSTOMER_EVENT3(EVENT_TYPE )

                                                      VALUES

                                                                        (

                                                                        :EVENT_TYPE);

END;

CALL SCHEMA.INS_CUSTOMER_EVENT_NULL96(NULL);

Anybody know why the above will not allow me to pass a NULL to the stored procedure? I've tried converting the event_type below but it's not allowing the value passed the orange statement so it can never get to the conversion statement.

I'm getting this error:

SAP DBTech JDBC: [7]: feature not supported: feature not supported: insert NULL value for EVENT_TYPE

[Former Member]

Hi Randi,  I am having the exact same issue.  I pass a null to a stored procedure and then try to insert that null into hana and I get the same error.   Did you ever get your original question resolved?  If so, can you share with the group?

Thanks,

Raphael

[Former Member]

All,

I did some more research and the issue is that you can not pass a null into a stored procedure. 

The web page, http://help.sap.com/hana/html/sql_call.html, says, " Parameters passed to a procedure are scalar constants and can be passed either as IN, OUT or INOUT parameters. Scalar parameters are assumed to be NOT NULL. Arguments for IN parameters of table type can either be physical tables or views. The actual value passed for tabular OUT parameters must be`?`."  

--Raphael

[ambrish_mishra]

Hi,

please check if the event_type supports NULL value. This might be a database concept issue.

please also explain your scenario.

Ambrish

[Former Member]

Hello

Even I am having the same problem: cannot pass NULL value to INT or VARCHAR input variables of stored Procedure. Please find my stored proc,

CREATE PROCEDURE <proc name>(IN emp_id INT, IN emp_name VARCHAR(15), OUT R1 VARCHAR(15))

    LANGUAGE SQLSCRIPT AS  TEMP INT; TEMP_IN INT;

BEGIN

IF :emp_id IS NULL THEN

    TEMP_IN:=0;

END IF;

SELECT COUNT(EMP_ID) INTO TEMP FROM <table name>  WHERE EMP_ID = :emp_id;

IF TEMP = 0 AND emp_id != 0 THEN

       INSERT INTO <table name> VALUES (:emp_id, :emp_name);

       R1:='INSERTED';

ELSEIF TEMP_IN = 0 THEN

       INSERT INTO <table name> VALUES (inc_int.NEXTVAL, :emp_name);

       R1:='INSERTED';

ELSE

       UPDATE <table name> SET EMP_NAME = :emp_name WHERE EMP_ID = :emp_id;     

       R1:='UPDATED';

END IF;      

END;

I am capturing the input variable into a temp variable, to make the value to '0' if it is NULL. But no luck.

Btw, I am calling this proc from SQL editor itself, using: CALL <proc name>(NULL, 'nullcheck',?)

Thank You

[former_member187794]

Hi Everyone

I am new to SAP HANA. If some one tried to call a Stored Procedure by passing NULL values. Could you suggest if we have to do any changes to the stored proc to accept NULL values or we need to do any changes while calling the stored proc?

When calling the stored proc (as defined above by Bala Dondeti) I am getting an error:

SAP DBTech JDBC: [7]: feature not supported: feature not supported: insert NULL value

Regards

Giri

[ambrish_mishra]

In case a target field is mandatory, make sure you create the tag (NULL value) within PI mapping.

Ambrish

[former_member187794]

Ambrish, I am trying to call the stored procedure from SQL editor itself. Not sure, what you mean by 'create the tag (NULL value" with in PI mapping'

Regards

Giri

[ambrish_mishra]

Hi Giri,

I thought you are using PI for Hana integration.

I think you should be looking into Hana specific forum for this query. However, I found this link which might help:

http://scn.sap.com/community/hana-in-memory/blog/2012/12/13/sps-5-quick-note--on-null-values

cheers,

Ambrish

[former_member187794]

thanks Ambrish