How to call a Stored Procedure with output type TABLE

[Former Member]

Hello all,

As i am new i dont know how to call a stored procedure with output parameter as tabletype, I want to call this from SAP HANA STUDIO,and from programming language like JAVA and PYTHON .

here is the description

Here i am creating a Tabletype

set schema EC2;

CREATE TYPE selecttypeall AS TABLE ("LINK" VARCHAR(1000), "DESC" VARCHAR(1000));

Then creating a stored procedure.

SET SCHEMA EC2;

DROP PROCEDURE outputselect;

CREATE PROCEDURE outputselect(in keyword varchar(100), out outall selecttypeall) as

begin

outall = SELECT "LINK","DESC" FROM "EC2"."HEALTH1" WHERE UPPER("DESC") LIKE UPPER(:keyword);

end

So how to call this stored procedure??? Just want to know

Regards

Qaiser Imam Khan

[former_member184768]

Hi,

Please refer to the following thread for the sample example

http://scn.sap.com/thread/3376895

Also please refer to the section 10.3.2 Calling SQL script from Java in HANA SQL script reference.

Regards,

Ravi

[rindia]

Hi Qaiser,

Call the procedure like this:

CALL "EC2"."outputselect" ('pass_value_of_keyword', null);

Regards

Raj

[Former Member]

Thanks for this answer Raj but i want the result in an output tabletype? So how can i do that while calling?

[ranjit_alapati]

Hi Qaiser,

Create a global temporary table which is exactly same as  table type(selecttypeall) like gt_selecttypeall and call your procedure as below

CALL "EC2"."outputselect" ('pass_value_of_keyword', gt_selecttypeall) with overview;

If you get the error: Invalid column name gt_selecttypeall, you need to change the order of
the input and output parmaters

-- Check result tab. It displays name of table type output parameter and table name.

Use select on temporary
table to retrieve result.


select * from gt_selecttypeall;

Best Regards,

Ranjit