How to create and Call Procedure

[former_member213277]

I experts,

I am facing the issue in creating the Procedure.

I have created a procedure as follows:

CREATE TYPE tt_mblnr AS TABLE( v_mblnr INTGER);

CREATE PROCEDURE demo_proc () LANGUAGE SQLSCRIPT READS SQL DATA AS

BEGIN

tt_mblnr = SELECT "MSEG"."SALK3" FROM "HANADBER5"."MSEG" ;

END;

When I execute above code in SQL Editor it works fine, but its giving me an error when I try to create same in PROCEDURE

and also could you please suggest me how see the output of the Procedure. I tried calling the Procedure in SQL editor as mentioned below but i am not getting any output.

CALL demo_proc() WITH OVERVIEW;

Thanks in advance

Regards,

Nagaraj

[Former Member · Accepted solution]

Hi Nagraj,

Please create a procedure as below for your scenario.I hope that it will work fine.

Schema  name :"P1763569281"

create type "P1763569281".art as table

(article_id int);

CREATE PROCEDURE "P1763569281".demo_proc4 (out tt_mblnr "P1763569281"."ART")

LANGUAGE SQLSCRIPT READS SQL DATA AS

BEGIN

tt_mblnr = SELECT  article_id FROM "EFASHION_TUTORIAL"."ARTICLE_LOOKUP" ;

END;

call "P1763569281"."DEMO_PROC4"(?)   : it will display the data record set.

call "P1763569281"."DEMO_PROC4"(?) with overview : Out put data  will be stored in temporary table.

view the data :

select * from temporary table

ex:select * from "P1763569281"."TT_MBLNR_512E5AFC34A01051E10000007F000002"

Thanks

Basha.

[former_member213277 · Accepted solution]

Thanks Basha,

can you please tell me how to find out the temporary table name

Regards,

Nag

[former_member184768 · Accepted solution]

Hi Nagaraj,

Rather than using a temporary table, why don't you declare the procedure with NAMED result view, something like

LANGUAGE SQLSCRIPT READS SQL DATA WITH RESULT VIEW V_PROC_OUTPUT

You can then select from the result view V_PROC_OUTPUT.

Regards,

Ravi

[Former Member · Accepted solution]

Hi Nagaraj,

Once you excecute the command  call "P1763569281"."DEMO_PROC4"(?) with overview in SQL editor,The result tab  dispalys the table and output variabl  name.

2.  select *  from temporary table name

Thanks

Basha.

[former_member213277]

Thanks Lars, Basha and Ravindra for your valuable suggestions

Regards,

Nagaraj

[lbreddemann]

Hi Nagaraj,

where's your OUT parameter? I thought that's what you created the table type for...

And what exact error code do your receive?

Usually, procedures provide a OUT parameter that can be consumed via a host parameter in the procedure call. This would look similar to this:

call demo_proc (?)

HANA studio will retrieve the result table bound to the parameter and display the result.

No need for the WITH OVERVIEW clause here.

- Lars