SAP HANA : How to insert the result of stored procedure into local temporary table

Question

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.

Answer 1

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.