Procedure with result view

[felix_guldner]

Hi,

I successfully created a procedure with the .hdbprocedure syntax. Now I wanted to add a result view to this procedure (which has only one output parameter) as described in the SQLScript Reference Guide. Unfortunately I always get the simple error message "The With Result View statement is not permitted". We are using HANA revision 96.

Are there any restrictions for using result views in procedures which the reference guide does not mention? The strange thing is that even the simple result view example as described in the guide does not work in my case.

Additional question:
Is it possible to generate an oData service on top of the result view of a procedure and in that way consume this procedure in a UI5 application?

Thanks and best regards

Felix

[pfefferf · Accepted solution]

Hello Felix,

how locks your call statement.

Did you pass an existing table name or a "?" as argument for the output parameter?

In case of an existing (column/row/temporary) table the result data is written into that table, in case of a "?" a temporary table is generated and the name of the temporary table is returned as result from the procedure.

Regarding your additional question: In case you write your result into a column/row table (not a temporary one) you can access the data of course via an OData service. But you have to consider and react on concurrent accesses to the table in case the procedure can be called several times at the same time.

Best Regards,

Florian

[felix_guldner · Accepted solution]

Hello Florian,

the problem is not the call statement, but rather the activation of the procedure once I add the "with result view" option. Without this option I can successfully activate and call the procedure via a call statement from the console or via JDBC.

My intention was to add a result view to the procedure and then call this procedure via a select on this view. This is described in the SQLScript Reference ("ProcView" is the result view of the procedure and  "id" is an input parameter):

SELECT * FROM ProcView PLACEHOLDER."$$id$$"=>'5');

I don't want to write the result of the procedure into a non-temporary table because I have similar doubts concerning concurrency control. Despite that, a simple SELECT on the result table would not trigger the execution of the procedure, so it would somehow be necessary to call the procedure separately in advance.

Best regards

Felix

[pfefferf · Accepted solution]

Hi Felix,

sorry, mixed that up with "WITH OVERVIEW".

Regarding "WITH RESULT VIEW". I think you try to create your procedure via a repository object (e.g. HDBPROCEDURE). For that way of creating a procedure the "WITH RESULT VIEW" statement is (still) not supported.

You can use that statement only in case you create a catalog procedure directly via a "CREATE PROCEDURE" statement (executed e.g. within SQL console).

Best Regards,

Florian

[RichHeilman · Accepted solution]

You should use a table UDF(.hdbtablefunction) for this requirement instead. There is no plans to support WITH RESULT VIEW in .hdbprocedure

Cheers,

Rich Heilman

SAP HANA Product Management