Question Re: Procedure overloading in HANA SQL in Technology Q&A

Procedure overloading in HANA SQL

former_member768160 — Fri, 17 Dec 2021 06:49:04 GMT

I want to create 2 stored procedures with different sets of params. is it possible in HANA SQL.

Example:

Create Table DEMO_TABLE ( ID INTEGER, NAME VARCHAR(10), NAME2 VARCHAR(10) );

CREATE PROCEDURE pkg_demo_entry(IN ID             INTEGER, IN NAME 		    NVARCHAR(10), IN NAME2        NVARCHAR(10) ) LANGUAGE SQLSCRIPT AS BEGIN INSERT INTO DEMO_TABLE (ID, NAME, NAME2) VALUES(:ID, :NAME, :NAME2); END ;

CREATE PROCEDURE pkg_demo_entry(IN ID             INTEGER, IN NAME 		    NVARCHAR(10) ) LANGUAGE SQLSCRIPT AS BEGIN INSERT INTO DEMO_TABLE (ID, NAME, NAME2) VALUES(:ID, :NAME, 'XXXXX'); END ;

Re: Procedure overloading in HANA SQL

dvankempen — Fri, 17 Dec 2021 07:48:33 GMT

Hi Santosh,

It is but you would have to create them in two different schema's.

Re: Procedure overloading in HANA SQL

Joerg_Brandeis — Fri, 17 Dec 2021 07:58:08 GMT

Hi Santosh,

overloading of procedures is not possible. You have to create a procedure with a different name for each parameter set.

For some usecases, an optional IN Parameters with a DEFAULT value could be a solution.

Regards,
Jörg

Re: Procedure overloading in HANA SQL

lbreddemann — Tue, 21 Dec 2021 02:03:03 GMT

An alternative option is to take the approach used by the PAL procedures:

use a generic “parameters” table structure to communicate parameters to the procedure.

This makes calling the procedure more complex as one first has to establish the table structure (often a temporary table), fill it with the required parameters, and then call the procedure with the reference to the table. On the other hand, this call sequence will be identical for any call to the procedure.

The different functional variations of the procedure can then either be handled in different branches of the “main” procedure or via sub-procedured.

Anyhow, you probably get the idea.