0
votes

I am trying to store the result of my read/write stored procedure in local temporary table. After creating the temp table i am writing -

INSERT INTO #TMP call SPName;

to store the result in temp table but it says feature not supported. Is there anyway to store the result of stored procedure in temp table. I don't want to alter the definition of already build SP but want to store the result in temp table to join with another set of statement to get my final result.

2
I see two answers proposing alternative solutions, but none of them say whether HANA offers the exact functionality that Prasoon needs.Ant_222

2 Answers

2
votes

Have you tried to use an output table type as one of your stored procedure parameter? And then use that out parameter in your stored procedure to return the result?

This is the typical approach used in many example in the documentation.

DROP   TYPE TT_MYTYPE;
CREATE TYPE TT_MYTYPE AS TABLE(A VARCHAR (100), B INT);

DROP   PROCEDURE MYPROC;
CREATE PROCEDURE MYPROC (OUT OUTPUT_TABLE TT_MYTYPE )
   LANGUAGE SQLSCRIPT SQL SECURITY INVOKER 
   AS
   BEGIN
     OUTPUT_TABLE = SELECT 'Test' as A, 3 as B FROM DUMMY;
   END;

-- output table is created dynamically and in the overview you can get the table name
CALL MYPROC( ? ) with overview;

-- pass an output parameter table to receive the result
DROP   TABLE MYOUTPUT;
CREATE TABLE MYOUTPUT LIKE TT_MYTYPE;

CALL MYPROC( MYOUTPUT ) with overview;
SELECT * FROM MYOUTPUT;

Hope this helps.

1
votes

You can use the WITH RESULT VIEW extension of the procedure definition. For example, if I use the @Abdel's code

CREATE PROCEDURE MYPROC (OUT OUTPUT_TABLE TT_MYTYPE )
   LANGUAGE SQLSCRIPT 
   READS SQL DATA
   WITH RESULT VIEW myView
   AS
   BEGIN
     OUTPUT_TABLE = SELECT 'Test' as A, 3 as B FROM DUMMY ;
   END;

Then you can simply execute a SELECT statement on myView as follows

SELECT * FROM myView ;

For more details on how you can use With Result View to select data from HANA procedure, you can refer to given tutorial.

If your procedure accepts input parameters, the view created by "with result view" can be queried just like a parametric view