1

How can I read the content of an out table type parameter of a procedure in SAP HANA SQL Script?

Sample Procedure:

create procedure "MYSCHEMA".ReturnTypeTest(out OUTPUT_TABLE "MYSCHEMA"."RESOUT")
as
begin
    create local temporary table #temp ("COL1" bigint, "COL2" bigint, "COL3" bigint);

    insert into #temp values(1, 2, 3);
    insert into #temp values(4, 5, 6);
    insert into #temp values(7, 8, 9);
    
    OUTPUT_TABLE = select * from #temp;

    drop table #temp;
end;

Table Type (Out Parameter):

create type "MYSCHEMA"."RESOUT" as table ("COL1" bigint, "COL2" bigint, "COL3" bigint);

When I call the procedure as below, it displays entire content in SAP HANA Studio's result pane but how can I get it programmatically?

call "MYSCHEMA"."RETURNTYPETEST"(?);
Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
Amiga500
  • 89
  • 1
  • 6

4 Answers4

0

Output variables from procedures can only be assigned to variables in an SQLScript context.
An exception to this is the default resultset that gets bound to the last SELECT command executed in the procedure.
If your intention is to produce something that can be SELECTed, you may want to use a table typed user defined function (TUDF) instead.

Two comments to your example code:

  1. using temporary tables is not a good idea if performance is of concern for your application. While imperative code often appears to be more intuitive, it really tends to block parallelism during statement execution.

  2. It's very (too) easy to overload a single procedure function wise by including data manipulation, computation and resultset returns. If possible, rather opt for smaller functional units and split up the functionality into multiple objects.

Ok, after you clarified that you actually just want to access the resultset in SQLScript and not in plain SQL, I can add this to my answer: Check what I wrote in the first sentence! You can simply assign any output variable from a procedure to a corresponding variable. The documentation has examples on that HANA documentation: CALL. For example, if your output structure is a table that contains user information it may look like this:

DECLARE uaccounts TABLE (USERID bigint, USERNAME NVARCHAR(256), CREATED date);
DECLARE expdate date := current_date;

/* In this example the procedure 'get_expired_useraccounts_by date' has got
   the IN parameter expiry_date (date) and 
   the OUT parameter expired_accounts (table structure).

  By assigning the variable uaccounts to the OUT parameter, the result set
  automatically gets bound to uaccounts.*/

call get_expired_useraccounts_by_date  (:expdate, :uaccounts);

/* from here you can use :uaccounts like a table variable*/

SELECT count(*) FROM :uaccounts;

All this is, of course, part of the reference documentation and the developer guides...

Lars Br.
  • 9,949
  • 2
  • 15
  • 29
  • Thanks for your reply but since functions are read-only in SAP Hana, it can't be used in our situation. Do we have any other option other than TUDF? – Amiga500 Jan 26 '17 at 09:50
  • Ok, so you want a procedure that changes data and returns a result set that can be read from plain SQL? Nope, I don't see a way to do that. For your example, you could simply run the procedure and select your modified data from the temp. table - the data lives as long as your session does, so you could abuse this as a way to hand over the data. But realise that this is rather bad design and that most of the data manipulations you might want to do can be done without writing/updating data in tables. The way you chose here is the worst performing approach. – Lars Br. Jan 29 '17 at 00:59
  • No, I'm not looking for a way to read the output parameter from plain SQL. I need to call this procedure in the body of another procedure and process what it returns. But I don't know how to handle table type output. If it was a scalar out parameter, I can simply declare a variable for it but I couldn't figure out what data structure is used for table type out parameters. – Amiga500 Jan 29 '17 at 06:52
0

Could you please check following SQLScript

declare lt_list "MYSCHEMA"."RESOUT";
call "MYSCHEMA"."RETURNTYPETEST"(lt_list);
select * from :lt_list;

This should display the output parameter table using the last SELECT statement

Eralper
  • 6,461
  • 2
  • 21
  • 27
0

The answer, after understanding the context with Lars' Q&A, is: define a table variable in your caller procedure code

DECLARE temp TABLE (n int); DECLARE temp MY_TABLE_TYPE;

Then assign the output param of the callee to it.

https://help.sap.com/viewer/de2486ee947e43e684d39702027f8a94/2.0.01/en-US/ea5065d06d14426799d879234d8e3e7b.html

Sefan L.
  • 1
-1

You can query the system views for metadata Please check following SQLScript Select

select table_type_schema, table_type_name, *
from PROCEDURE_PARAMETERS 
where 
schema_name = UPPER('MYSCHEMA') and
procedure_name = UPPER('ReturnTypeTest') and
parameter_name = UPPER('OUTPUT_TABLE')

I hope it helps

Eralper
  • 6,461
  • 2
  • 21
  • 27