UPSERT SP on hana that can: 1. insert new row. 2. update *different column* on each sp call

[former_member1184990]

I have a table with the following columns: id, name, age, address.

I want to do an UPSERT SP on hana that can:

1. Insert a new row.
2. Update different columns on each sp call.

The SP looks like this:

PROCEDURE UPSERT_SP (
   ID varchar(10),
   NAME varchar(20),
   AGE int,
   ADDRESS varchar(50)
)

BEGIN
UPSERT "MT_TABLE"AS MT
VALUES(
   :ID,
   IFNULL(:NAME,    MT."NAME"),
   IFNULL(:AGE,     MT."AGE"),
   IFNULL(:ADDRESS, MT."ADDRESS"))
WHERE MT."GUID"=:GUID;
END;

Now, if I call my SP I want this to happen:

• CALL UPSERT_SP('new_id', 'new_name', 55, 'new_address') - 'new_id' doesn't exist in the table, so a new row will be created.
• CALL UPSERT_SP('new_id', null, 100, null) - 'new_id' exist in the table so the row of 'new_id' will update only the AGE column because the two others (name, address) are 'null'.

I know that the sp I wrote works in SQL Server Manager, but for some reason SAP HANA doesn't let me do this. When I try to activate the sp i get the following error:

column not allowed: MT.NAME

How can I get the result I'm trying to achieve (I've already tried UPSERT with sub-query and it didn't work).

[seVladimirs]

Seems that you are trying achieve that UPSERT query is ignoring NULL values. If we would use UPSERT query without modification we would expect that during second execution of the procedure additionally to age also new_name and new_address are updated (will have value NULL). To implement such workaround you can check code snippet example below:

PROCEDURE "UPSERT_SP"(   
				ID varchar(10),
				NAME varchar(20),
				AGE int,
				ADDRESS varchar(50)
)
	LANGUAGE SQLSCRIPT
	SQL SECURITY INVOKER
	AS
BEGIN
	lt_existing_values = SELECT ID, 
								IFNULL(:NAME, NAME) AS NAME, 
								IFNULL(:AGE, AGE) AS AGE, 
								IFNULL(:ADDRESS, ADDRESS) AS ADDRESS 
						FROM "test.mt_tables" where id = :id;
							 
	IF IS_EMPTY(:lt_existing_values) THEN	
		INSERT INTO "test.mt_tables" VALUES (:ID, :NAME, :AGE, :ADDRESS);
	ELSE 
		UPSERT "test.mt_tables" SELECT ID, NAME, AGE, ADDRESS FROM :lt_existing_values;
	END IF;
END

[michael_eaton3]

That won't ever INSERT a new row as the subquery won't return anything in the case of new data.

[seVladimirs]

right, didn't execute my code. why you simple can use UPSERT as per design and pass instead of NULL values actual same values? if it is specific requirements and you still need it - you can always use power of SQLScript and check if values are empty -

PROCEDURE "UPSERT_SP"(   
				ID varchar(10),
				NAME varchar(20),
				AGE int,
				ADDRESS varchar(50)
)
	LANGUAGE SQLSCRIPT
	SQL SECURITY INVOKER
	AS
BEGIN
	lt_existing_values = SELECT ID, 
								IFNULL(:NAME, NAME) AS NAME, 
								IFNULL(:AGE, AGE) AS AGE, 
								IFNULL(:ADDRESS, ADDRESS) AS ADDRESS 
						FROM "test.mt_tables" where id = :id;
							 
	IF IS_EMPTY(:lt_existing_values) THEN	
		INSERT INTO "test.mt_tables" VALUES (:ID, :NAME, :AGE, :ADDRESS);
	ELSE 
		UPSERT "test.mt_tables" SELECT ID, NAME, AGE, ADDRESS FROM :lt_existing_values;
	END IF;
END