Problem: output parameter with table type of a procedure

[Former Member]

Hi HANA Experts,

I am using HANA Rev 52.1. I want to use the output parameter with table type of a procedure TEST1 . And this procedure TEST1 will be called in other procedure TEST2. Unfortunately TEST2 can not get the data from output from TEST1 (see Check 1 in following script). If I call the procedure TEST1 direct from SQL console, it works.

Can you help me?

Best Regards

--------------------------------------------------

SET SCHEMA MY_SCHEMA;

--------------------------------------------------

create type MY_TABLETYPE as table (COL nvarchar(100));

--------------------------------------------------

CREATE PROCEDURE TEST1 (out vOut MY_TABLETYPE)

LANGUAGE SQLSCRIPT AS

BEGIN

      vOut = select 'abcd1234' as COL from dummy;

END;

-----------------------------------------------

CREATE PROCEDURE TEST2

LANGUAGE SQLSCRIPT AS

BEGIN

      create global temporary table MY_TEMP (COL nvarchar(100));

      call TEST1(MY_TEMP) with overview;

      select * from MY_TEMP;

      drop table MY_TEMP;

END;

-----------------------------------------------

-- Check 1: not work

call TEST2;  -- Result is empty.

-----------------------------------------------

-- Check 2: ok

call TEST1(?); -- Result is ok

-----------------------------------------------

-- Check 3: ok

create global temporary table MY_TEMP_1 (COL nvarchar(100));

call TEST1(MY_TEMP_1) with overview;

select * from MY_TEMP_1; -- Result is ok

drop table MY_TEMP_1;

-----------------------------------------------

[Former Member · Accepted solution]

Hi,

So this works as it should. You defined procedure 2 to:

1) Create a global temp table

2) Call test1

3) Select * from MY_TEMP (and do nothing with it, so this line will be removed by the optimizer)

4) drop the global temp table.

5) Return nothing

So procedure2 should return nothing.

I have modified and commented your code to do what I think you are expecting.

John

SET SCHEMA system;

--------------------------------------------------

drop type my_tabletype;

create type MY_TABLETYPE as table (COL nvarchar(100));

--------------------------------------------------

DROP PROCEDURE TEST1;

CREATE PROCEDURE TEST1 (out vOut MY_TABLETYPE)

LANGUAGE SQLSCRIPT AS

BEGIN

      vOut = select 'abcd1234' as COL from dummy;

END;

-----------------------------------------------

DROP PROCEDURE TEST2;

CREATE PROCEDURE TEST2 (out vOut MY_TABLETYPE)

LANGUAGE SQLSCRIPT AS

BEGIN

  -- Note that local tables are implicitly typed so you don't need to define them

  CALL TEST1(lt_my_temp);

  -- Make sure you reference local tables with :lt_my_temp

  -- It is good practice never to use SELECT *

  vOut = SELECT COL FROM :lt_my_temp;

END;

-----------------------------------------------

-- Check 1: not work

call TEST2(?);  -- Result is ok

-----------------------------------------------

-- Check 2: ok

call TEST1(?); -- Result is ok

-----------------------------------------------

-- Check 3: ok

create global temporary table MY_TEMP_1 (COL nvarchar(100));

call TEST1(MY_TEMP_1) with overview;

select * from MY_TEMP_1; -- Result is ok

drop table MY_TEMP_1;

[Former Member · Accepted solution]

Hi John,

Thank you very much for your help! It works!!

Yujun