0

In SAP HANA i can create a procedure with in and output parameters. Even without output parameter the procedure can output a table.

I notice three versions of output in stored procedures:

  1. a select at the end of the procedure - without declaring the structure.
  2. an output parameter out parameter varhcar(100)
  3. an implicit table definition returns table (var1 varchar(10)) after function parameter and before keyword LANGUAGE SQLSCRIPT

What is the difference of these and how can I reuse each of these output parameters in other stored procedures?

The only one I am aware of is

  1. call procedure(input1, input1, outputVar)

Unfortunately I don't know how to bound an SQL result to output parameters without creating a physical table.

Reason for this question

Issue 1

Function callBuildJoinOn returns empty result. Due to that loop in SP_BUILD_JOIN_ON is not executed - but the list is build in split string (both tested)

Proceedure callBuildJoinOn
...
in colTable1 nvarchar(200)
out columnsTable1 "SCHEMA"."package::TT.STRING_LIST"
call "SCHEMA"."package::SP_SPLIT_STRING"(colTable1, columnsTable1);
call "SCHEMA"."package::SP_BUILD_JOIN_ON"(:columnsTable1, :columnsTable2, :joinOn);

SP_BUILD_JOIN_ON
columnsTable1 "SCHEMA"."package::TT.STRING_LIST"
declare cursor columnList for
  select * from :columnsTable1;
for col as columnList do
    joinOn := joinOn || 'a.' || col.item;
end for;

Why split in two functions?

Declare of cursor results in compiler error if after a call statement

Thorsten Niehues
  • 13,712
  • 22
  • 78
  • 113

2 Answers2

1

There is even a fourth option to get to a result set from a HANA SQLScript procedure: the result view (not supported anymore as of HANA2).

But let's not stir up more confusion. The different options can be used in different scenarios:

  1. Procedure IN/OUT parameters are used to get data into and results out of procedures. The OUT parameters can either be bound to other SQLScript variables (when you call the procedure from another procedure), or a HANA client can read the result sets that get created for each OUT parameter of type TABLE). For example, a JDCB client would find multiple result sets after the procedure execution and would fetch those.

  2. Table functions, that are functions that return a table, can be used just like tables or views:

    SELECT * FROM <tablefunction>( in_param1, in_param2, ...);

    This provides an easy option to read back whole tables from functions or to simulate parameterised views.

  3. The remaining option you mentioned is the so-called "default result set". This really is only useful for consumption in the SQL editor or in a HANA client that fetches all result sets after procedure execution. The default result set cannot be bound to any SQLScript variable and cannot be referenced in SQL commands.

Lars Br.
  • 9,949
  • 2
  • 15
  • 29
  • Thx for shedding some light into this topic. I am still struggling to reuse the output of this https://stackoverflow.com/a/27709213/993494 split function: I can't assign `rst` to an output variable :/ – Thorsten Niehues Aug 17 '17 at 12:08
  • If it's about the split functionality itself, there are `REGEXPR` functions available for quite some time now, instead. If it's about the actual assignment, can you please open a new question with a reproducible example of the problem? – Lars Br. Aug 17 '17 at 22:02
  • Yes this thread is about parameters and how to work with them .. The question is also not fully answered: how to re-use the return table(...) of a procedure in another procedure? – Thorsten Niehues Aug 18 '17 at 07:32
  • I did see the regex but not split If you know how to split with regex - without a loop I'd be happy to read your answer in this thread: https://stackoverflow.com/a/27709213/993494 – Thorsten Niehues Aug 18 '17 at 07:33
  • further refined question. Problem passing parameter between functions seems not to work. Also declare cursor after call results in compiler error – Thorsten Niehues Aug 18 '17 at 08:22
0

Problem was that joinOn was defined as output parameter: out joinOn nvarchar(1000)

this resulted that:

  1. the variable was null
  2. the variable was not populated: joinOn := joinOn || 'a.' || col.item; resulted in null !

Solution

  1. Change out to inout
  2. initialize parameter joinOn := ''
Thorsten Niehues
  • 13,712
  • 22
  • 78
  • 113