Skip to Content
0
Sep 25, 2019 at 09:18 AM

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

261 Views

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).