cancel
Showing results for 
Search instead for 
Did you mean: 

How to call HANA store procedure with input table variable in a SQL console?

Please provide syntax for calling a HANA stored procedure with input table variable in a SQL console ?


Accepted Solutions (1)

Accepted Solutions (1)

former_member184871
Contributor
0 Kudos

Hi Sravan,

Please refer  http://help.sap.com/hana/SAP_HANA_Developer_Guide_en.pdf > Page Number  341, TOPIC : SQL Extensions for Procedures

Hope it helps.

Regards

Kumar

lbreddemann
Active Contributor
0 Kudos

Slightly missed the point.

The question was about providing an INPUT variable.

An in fact it is not possible to call a procedure with an table type input variable from the SQL console.

You have to build a wrapper to call such a procedure:


create type myusers as table (user_name nvarchar(60));

create procedure count_users (IN user_tab myusers, OUT user_count bigint )

language sqlscript

as

begin

    select count(*) into user_count from :user_tab;

end;

call count_users (? , ?)

Could not execute 'call count_users (? , ?)' .

SAP DBTech JDBC: [7]: feature not supported:

Parameterized input table parameter is not allowed: line 1 col 19 (at pos 18)

A wrapper for this could look like this:


create procedure call_cu (out user_count bigint)

language sqlscript

as

begin

    v_users = select user_name from users;

    call count_users (:v_users, :user_count);

end;

call call_cu (?)

--> 28

Unlike SQL*Plus for PL/SQL, the SQL console of SAP HANA is not a SQL Script runtime shell.

- Lars

former_member184871
Contributor
0 Kudos

Hi Lars,


Yup my bad, I missed the key point of the question. Thanks for pointing and correcting it.



Regards

Kumar

0 Kudos

Hi,

You can call procedures with table type input parameters from SQL Console. But, you cannot specify these as ?s. What you can do is create temporary tables (or physical tables, views etc) and pass these as the parameters.  You can find examples for this in PAL user guide. Here is sample from there:


--CREATE TABLES

CREATE COLUMN TABLE PAL_KMEANS_DATA_TBL LIKE PAL_KMEANS_DATA_T;

INSERT INTO PAL_KMEANS_DATA_TBL VALUES (0 , 0.5, 'A', 0.5);

INSERT INTO PAL_KMEANS_DATA_TBL VALUES (1 , 1.5, 'A', 0.5);

-- and more

CREATE LOCAL TEMPORARY COLUMN TABLE #PAL_CONTROL_TBL LIKE PAL_CONTROL_T;

INSERT INTO #PAL_CONTROL_TBL VALUES ('THREAD_NUMBER',2,null,null);

INSERT INTO #PAL_CONTROL_TBL VALUES ('GROUP_NUMBER',4,null,null);

-- and more

CREATE COLUMN TABLE PAL_KMEANS_ASSIGN_TBL LIKE PAL_KMEANS_ASSIGN_T;

CREATE COLUMN TABLE PAL_KMEANS_CENTERS_TBL LIKE PAL_KMEANS_CENTERS_T;

--CALL PROCEDURE 2 input parameters, 2 output parameters

-- 1st parameter is a column table

-- 2nd parameter is a temporary table

-- output parameters are physical tables these could be ?s

CALL _SYS_AFL.PAL_KMEANS_PROC(PAL_KMEANS_DATA_TBL, #PAL_CONTROL_TBL,

PAL_KMEANS_ASSIGN_TBL, PAL_KMEANS_CENTERS_TBL)

with OVERVIEW;

SELECT * FROM PAL_KMEANS_CENTERS_TBL;

SELECT * FROM PAL_KMEANS_ASSIGN_TBL;

Regards,

Dinu

Answers (3)

Answers (3)

edna_teich
Explorer
0 Kudos

You can also put the call inside a begin end statement and declare your input table variable.

do ()
begin
-- DECLARE TABLE VARIABLE
declare lt_tab "MyType";

-- FILL TABLE VARIABLE
:lt_tab.INSERT(('A', 'B', 4), 1 );

call "MyProcedure" (
  IT_TAB => :lt_tab,
  ET_DATA => ?
);

end;

Former Member
0 Kudos

This message was moderated.

former_member210482
Active Participant
0 Kudos

There is another scenario where you will be using particular fields from the input table. So it can be done in this way.



  1. create type myinput as table (prod_id nvarchar(20), cust_id nvarchar(20)); 
  2. create procedure myprocedure (IN input_table myinput, OUT output_table myinput) 
  3. language sqlscript 
  4. as 
  5. begin 
  6. output_table = select t1.prod_id, t1.cust_id, count(t1.prod_id)
  7.                         FROM product_table t1, :input_table t2
  8.                         WHERE t1.prod_id = t2.prod_id and t1.cust_id= t2.cust_id;
  9. end;
Former Member
0 Kudos

Hi All,

i am creating input parameter in hana graphical represent calculation views.

while creating input parameter i selected derived from stored procedure.

after that i select stored procedure it was giving below error

* Procedures with output parameters of type Table not supported

The below code was my procedure

BEGIN

COLUMN_SEC=SELECT MAX(COLUMN_SEC) AS COLUMN_SEC FROM (

SELECT

CASE WHEN ROLE_NAME LIKE '%ALL%' THEN 1 ELSE 0 END AS COLUMN_SEC

FROM SYS.GRANTED_ROLES

WHERE GRANTEE = SESSION_USER);

END;

and i created out parameter as well as

Please help me.

Regards,
Srini