title: SAP HANA Stored Procedure output types and how to reuse / capture output in SQLScript
body: <p>In SAP HANA i can create a procedure with in and output parameters. Even without output parameter the procedure can output a table.</p>
  
  <p>I notice three versions of output in stored procedures: </p>
  
  <ol>
  <li>a select at the end of the procedure - without declaring the structure.</li>
  <li>an output parameter <code>out parameter varhcar(100)</code></li>
  <li>an implicit table definition <code>returns table (var1 varchar(10))</code> after function parameter and before keyword <code>LANGUAGE SQLSCRIPT</code></li>
  </ol>
  
  <p><strong>What is the difference of these and how can I reuse each of these output parameters in other stored procedures?</strong></p>
  
  <p>The only one I am aware of is </p>
  
  <ol start="2">
  <li><code>call procedure(input1, input1, outputVar)</code></li>
  </ol>
  
  <p>Unfortunately I don't know how to bound an SQL result to output parameters without creating a physical table.</p>
  
  <p><strong>Reason for this question</strong></p>
  
  <p><strong>Issue 1</strong></p>
  
  <p>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)</p>
  
  <pre><code>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;
  </code></pre>
  
  <p><strong>Why split in two functions?</strong></p>
  
  <p>Declare of cursor results in compiler error if after a call statement</p>