Skip to Content
0
Jun 12, 2020 at 08:21 AM

SAP HANA stored procedure with multiple input parameter accepting multiple values

1161 Views

I am trying to achieve a stored procedure which reads data from multiple input parameters which accept multiple values. These values are in turn checked to delete data from user maintained tables. The IP values are taken from UI5 entries.

DELETE FROM "schema1"."xxx.yyy.UI5.Hierarchy.V1::T_111" where "ORG_ID" IN (:IP_ORG_ID) AND "VERSION" IN (:IP_VERSION);

DELETE FROM "schema1"."xxx.yyy.UI5.Hierarchy.V1::T_222" where "ORG_ID" IN (:IP_ORG_ID) AND "VERSION" IN (:IP_VERSION);

DELETE FROM "schema1"."xxx.yyy.UI5.Importables::T_333" where "ORG_ID" IN (:IP_ORG_ID) AND "VERSION" IN (:IP_VERSION);

I am unable to use APPLY_FILTER to use the multiple inputs as concatenation string as it is not supported with DML commands in sql procedure

--DECLARE FILTER_ORG VARCHAR(5000) := 'ORG_ID in ( ' || :IP_ORG_ID || ')';

--DECLARE FILTER_VERSION VARCHAR(5000) := 'VERSION in ( ' || :IP_VERSION || ')';